Microsoft SQL Server commonly known as MSSQL Server, is a relational database management system (RDBMS) developed by Microsoft Corporation. It is used to store and manage large amounts of data and can be used in a variety of applications, including web-based, client/server, and standalone applications.

MS SQL Server finds use in enterprise sectors, this includes financial services, e-commerce and healthcare. This is mainly because it is capable of handling a large number of concurrent users and provides a secure and reliable platform for storing and managing sensitive data. MSSQL also supports a large number of programming languages and platforms such as .NET, Java, Node.js etc. This makes it a versatile database platform to be used in a wide range of applications.

MSSQL offers a range of features that include:

  • Scalability: It can handle large amounts of data and scale to accommodate a growing number of users and applications.
  • Security: It provides robust security features, including encryption, access control, and auditing, to protect data and ensure compliance with regulations.
  • High availability: It includes features such as clustering and replication to ensure high availability and minimize downtime.
  • Integration: MSSQL integrates with a variety of programming languages and platforms, including .NET, Java, and Node.js, making it easy to develop and deploy applications.
  • Business intelligence: It also includes advanced analytics and reporting features, such as data mining, data warehousing, and OLAP (Online Analytical Processing), which enable users to gain insights from large amounts of data.
  • Performance: It provides features such as query optimization and indexing to ensure fast and efficient data retrieval and processing.
  • Cloud capabilities: MSSQL can be deployed on-premises or in the cloud, with support for hybrid cloud scenarios.

Join me in this guide as we dive into how to manage Databases, Users and Permissions in MS SQL Server 2022.

Getting Started

Before we plunge into the nub of this matter, you need to have the MSSQL Server 2022 installed on your system. This can be done in a number of ways depending on your environment. Below are some of the dedicated guides to help you install the MSSQL server 2022.

Once installed, proceed as shown below

1. Install SQL Server command-line tools

To manage databases on MS SQL Server 2022, you can use various tools, including SQL Server Management Studio (SSMS), Azure Data Studio, and command-line tools.

For this guide, we will learn how to manage the MSSQL Server 2022 databases using the command line. We will install SQL Server command-line tools: sqlcmd and bcp.

Depending on your system, add the repository and install sqlcmd and bcp using the commands below.

  • Debian/Ubuntu

Begin by adding the repository to the system.

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Once added, update your system and install sqlcmd and bcp:

sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

Now export the environment PATH:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
  • On Rocky Linux/Alma Linux/CentOS/RedHat

Add the repository with the command:

curl https://packages.microsoft.com/config/rhel/8/prod.repo | sudo tee /etc/yum.repos.d/msprod.repo

Once added, install the sqlcmd and bcp packages:

sudo yum remove mssql-tools unixODBC-utf16-devel
sudo yum install mssql-tools unixODBC-devel

Now export the environment PATH

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
  • On SLES

It is also possible to install mssql-tools on SUSE Linux. Begin by adding the repository:

sudo zypper addrepo -fc https://packages.microsoft.com/config/sles/12/prod.repo
sudo zypper --gpg-auto-import-keys refresh

Once added, install the mssql-tools with the command:

sudo zypper install mssql-tools unixODBC-devel

Export the environment:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
  • On MacOS/OS-X

You can install sqlcmd and bcp on MacOS/OS-X using HomeBrew. Add the repo with the command:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release

Next, install mssql-tools with the command:

brew update
brew install mssql-tools

2. Manage Databases on MSSQL Server 2022

Now we can use the Transact-SQL language statements to manage the MSSQL Server 2022. First, access the database using the command with the below syntax:

sqlcmd -S IP_ADDRESS -U sa -P '<YourPassword>'

In the command, there are several flags:

  • -S : Specifies SQL Server name/IP Address
  • -U : Sets username
  • -P : Provides user password

The default user on MSSQL is SA and the password was provided during the installation. Below is a sample execution.

Manage Databases Users and Permissions in MSSQL Server 2022

Once connected to the MSSQL Server 2022, you can now manage the databases.

a. Create a Database on MSSQL

To create a database on MSSQL Server 2022, use a command with the below syntax:

CREATE DATABASE [database_name];

For example:

CREATE DATABASE sampledb;
GO

Once the database has been created, you can list the databases with the command:

SELECT Name from sys.databases;
GO

Sample output:

Manage Databases Users and Permissions in MSSQL Server 2022 1

Create Tables on MSSQL

Now on the above database, we can add tables. For this demo, I will add a simple table called my_table:

USE sampledb;
CREATE TABLE my_table ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT );
GO

To insert data into the table:

INSERT INTO my_table (id, name, age)
VALUES (1, 'John', 25)
GO

To view the data in the table:

SELECT id, name, age FROM my_table
GO

Sample Output:

Manage Databases Users and Permissions in MSSQL Server 2022 2

Delete a Database|Table on MSSQL

You can also delete a database or a table on MSSQL. To delete a table, use the command with the below syntax:

DROP TABLE [table_name]
GO

For example;

DROP TABLE my_table;
GO

To delete a database, use the command with the below syntax:

DROP DATABASE [database_name]
GO

For example:

DROP DATABASE sampledb;
GO

3. Manage Users and Permissions in MSSQL Server 2022

By default, MSSQL comes with one user, the admin user with the username “sa”. However, it is possible to create other users and grant them permission to the various databases.

In MSSQL, user management, there are several concepts:

  • Login: A login is required to connect to the MSSQL server instance. It is used to authenticate the user and grant permission to access the database engine. A login can be created using SQL Server Management Studio (SSMS) or using the T-SQL command CREATE LOGIN.
  • User: A user is a security principal that is associated with a specific database. It is used to grant permissions to access and modify data within the database. A user can be created using SSMS or using the T-SQL command CREATE USER
  • Scope: A login is a server-level object that exists in the master database and can be used to connect to any database on the server. On the other hand, a user is a database-level object that exists in the context of a specific database.
  • Authentication: A login is authenticated by the MSSQL server using Windows Authentication or SQL Server Authentication. In contrast, a user is authenticated by the database engine using the login credentials.
  • Permissions: Logins are granted server-level permissions, while users are granted database-level permissions. This means that a login can access any database on the server, but a user can only access the specific database it is associated with.

a. Create a Login User on MSSQL

As explained earlier, a login user is used to authenticate a user or an application connecting to the database server.

To create a login user, use the command with the below syntax:

CREATE LOGIN login_name WITH PASSWORD = 'password';
GO

For example;

CREATE LOGIN testuser WITH PASSWORD = 'Passw0rd!';
GO

Once created, you can use it to login to the MSSQL database server:

sqlcmd -S 127.0.0.1 -U testuser

Sample Output:

Manage Databases Users and Permissions in MSSQL Server 2022 3

By default, login does not have any permissions to access any databases or database objects. However, a login does have the ability to connect to the database server and can be granted server-level permissions to perform administrative tasks, such as creating databases or managing server settings. To assign permissions, proceed and tag a user to it.

To delete a login, use:

DROP LOGIN [username]
GO

To change the password for a login, use:

ALTER LOGIN [login_name] WITH PASSWORD = 'new_password';
GO

Replace [login_name] with the name of the login for which you want to change the password and 'new_password' with the new password, you want to configure.

b. Create a User on MSSQL

Once a login has been created, it a login must be associated with a database user in order to grant that user access to specific databases and database objects. Without this association, the login will not be able to access any databases. A user is used to grant access to specific databases and database objects to the login.

Now login as the admin user and add a user to the created login earlier. The syntax for the command is:

CREATE USER user_name FOR LOGIN login_name;

For example:

CREATE USER my_user FOR LOGIN testuser;
GO

To delete a user, use the command with the syntax:

DROP USER [username]
GO

To change the password, use;

ALTER USER [userName] WITH PASSWORD = 'New Password'
GO

View the available users with the command;

SELECT * FROM sys.database_principals WHERE Type = 'S'
GO

c. Grant Permissions to a Login|User in MSSQL

In MSSQL, there are several built-in user roles that are used to manage permissions and access to various resources. These roles are designed to simplify the process of granting permissions to users and groups. Here are some of the commonly used user roles in MSSQL:

Logins can have server-level permissions and roles, such as “sysadmin“, “securityadmin“, “dbcreator“, and “processadmin” while users have database-level permissions. A user can be granted permission to access database objects, such as tables, views, stored procedures, and functions. Users can be granted database-level roles, such as “db_owner“, “db_datareader“, “db_datawriter“, and “db_ddladmin“

  1. For Logins: (To grant these permissions, use the master database)
    • SysAdmin: This role has full administrative access to the MSSQL instance and can perform any action on the server.
    • DbCreator: This role can create, alter, and drop databases.
    • SecurityAdmin: This role allows users to manage logins, users, and roles.
    • BackupOperator: This role allows users to back up and restore a database.
    • BulkAdmin: This role allows users to execute bulk import and export operations.
    • DDLAdmin: This role allows users to create, alter, and drop database objects, such as tables, views, and stored procedures.
  2. For users:
    • DbOwner: This role has full administrative access to a specific database and can perform any action on that database.
    • DataReader: This role allows users to read all data from all user tables in a database.
    • DataWriter: This role allows users to insert, update, and delete data in all user tables in a database.

Privileges can be added to the created user|Login to be able to grant permissions, first connect to the MSSQL server as the admin user;

sqlcmd -S 127.0.0.1 -U sa

We can use the sp_addrolemember command to grant the permissions. You can add any of the above roles for the user.

  • Granting database writer role:
USE [database_name]
GO
EXEC sp_addrolemember 'db_datawriter', 'username'
GO
  • Granting database reader role:
USE [database_name]
GO
EXEC sp_addrolemember 'db_datareader', 'username'
GO
  • Granting dbcreator role:
USE master
GO
EXEC sp_addsrvrolemember 'dbcreator',  'loginname';
GO
  • Granting sysadmin role:
USE master
GO
EXEC sp_addsrvrolemember 'sysadmin', 'loginname'
GO

For the above commands to work, you need to replace database_name with the actual name of the database, and username with the actual login name that you want to grant the role to.

To demonstrate if it works, we can add the DBCreator role to the user:

USE sampledb
CREATE USER  my_user FOR LOGIN testuser;
GO
EXEC sp_addrolemember 'db_datareader', 'my_user';
GO

View the added roles for the user;

1> EXEC sp_helpuser 'my_user';
2> GO
UserName       RoleName     LoginName        DefDBName    DefSchemaName UserID     SID                                                                                                                                                                         
-------------- ------------ ---------------- ------------ ------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
my_user        public       testuser         master       dbo           7          0x138EB720C61D394F96CBD43E42A800E9                                                                                                                                          
1>

Once added, we will test if we are able to create and delete a database:

Manage Databases Users and Permissions in MSSQL Server 2022 4

d. Revoke Permissions to Login\Users on MSSQL

To revoke users’ permissions, use a command with the below syntax:

REVOKE [role] FROM [user_name];
GO

For example:

REVOKE db_datawriter FROM [user_name];
GO

To revoke login permissions, use:

ALTER SERVER ROLE [role] DROP MEMBER [login];

For example:

ALTER SERVER ROLE dbcreator DROP MEMBER testuser;

Verdict

That marks the end of this guide on how to manage Databases, Users and Permissions in MS SQL Server 2022. I hope this was informative.

See more on this page:

How To Setup SSH and MySQL Bastion Server using Warpgate

Install Azure Data Studio on Rocky Linux 8|AlmaLinux 8

Run Microsoft SQL Server 2022 in Docker / Podman Container

LEAVE A REPLY

Please enter your comment!
Please enter your name here