How to Create Login, User, and Grant Permissions in SQL Server

November 25th, 2021

In the article, we are going to examine how to create a new user account and grant/revoke permissions and roles on a database object, as well as how to check SQL Server user permissions on the database using T-SQL and dbForge Studio for SQL Server.

When it comes to protecting sensitive data and keeping the database environment secure, the management of database permissions and privileges takes center stage. For example, you want a specific person to be able to modify and manipulate personal data, while others can only view this data. In this case, database administrators will need to create a new user or role with specific permissions that can be assigned at the server, database, or schema levels. To implement this task, they can use T-SQL statements provided by SQL Server or third-party tools.

Overview the features dbForge Studio for SQL Server

To move on, we are going to dig deeper in the following how-tos:

Create a new login in SQL Server

Prior to creating a database user, you should first create a new login based on Windows Authentication, SQL Server authentication, a certificate, or an asymmetric key.

To add a new login, use the CREATE LOGIN statement. It creates a login connected to a SQL Server instance. The login will then be mapped to the specified user account. The syntax is as follows:

Windows Authentication

CREATE LOGIN login_name   
   FROM WINDOWS
[ WITH DEFAULT_DATABASE = database_name
| DEFAULT_LANGUAGE = language_name ];

SQL Server authentication

 CREATE LOGIN login_name 
  WITH PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
[ , SID = sid_value
  | DEFAULT_DATABASE = database_name
  | DEFAULT_LANGUAGE = language_name
  | CHECK_EXPIRATION = { ON | OFF }
  | CHECK_POLICY = { ON | OFF }
  | CREDENTIAL = credential_name ];

Certificate

CREATE LOGIN login_name
FROM CERTIFICATE certificate_name;

Asymmetric key

CREATE LOGIN login_name
FROM ASYMMETRIC KEY asym_key_name;

The table describes the arguments used in the CREATE LOGIN statements.

Argument Description
login_name Name of the login connected to the server.
database_name Name of the default database to which the login will be assigned.
language_name Default language for the login you create.
password Password for the login you create.
hashed_password Hashed value of the password for the login you create.
MUST_CHANGE Prompts to change the password upon the connection.
sid_value Value used to recreate a login. It can be used only for logins with SQL Server authentication. If sid_value is not set, SQL Server will assign a new SID.
CHECK_EXPIRATION Defines whether the password expiration policy is applied. It should be set to ON if you use the MUST_CHANGE option.
CHECK_POLICY When the argument is set to ON, it indicates that Windows password policy of the computer on which SQL Server is running should be applied to the login as well.
credential_name Name of the credential to be assigned to the SQL Server login.
certificate_name Name of the certificate to be mapped to the SQL Server login.
asym_key_name Name of the asymmetric key to be mapped to the SQL Server login.

CREATE LOGIN statement using Windows Authentication

To add a SQL Server user based on Windows authentication, run the CREATE LOGIN statement with the following arguments:

CREATE LOGIN JordanS 
FROM WINDOWS;

This will create a new login JordanS on a SQL Server instance using Windows authentication.

Create a SQL Server Authentication login

For example, let’s create a login JordanS with the password ‘pass123’ using SQL Server authentication. For this, run the following command:

CREATE LOGIN JordanS
WITH PASSWORD = 'pass123';

If you want to change the password at the first login, add the MUST_CHANGE argumen with enabled CHECK_EXPIRATION to the CREATE LOGIN statement:

CREATE LOGIN JordanS
WITH PASSWORD = 'pass123' MUST_CHANGE, 
CHECK_EXPIRATION = ON;

Create a login from a certificate

Now, let’s see how to create a login JordanS using a SQL Server certificate certificate123.

CREATE LOGIN JordanS
FROM CERTIFICATE certificate123;

Create a login from an asymmetric key

Finally, add a new login JordanS that uses an asymmetric key key_123 in SQL Server.

CREATE LOGIN JordanS
FROM ASYMMETRIC KEY key_123;

Create a new user using T-SQL

After the login was created, it is time to add a new user. The basic syntax is as follows:

CREATE USER <username> for login <login_name>

To create a new user, for example, Manager, for the login JordanM, execute the CREATE USER statement:

CREATE USER Manager FOR LOGIN JordanM;

The following system view sys.database_principals allows you to retrieve the list of all users created in the SQL Server database. Run the command to view that the user was created.

SELECT *
FROM AdventureWorks2019.sys.database_principals;

The output should be as follows:

Retrieve the list of all available users created in the SQL Server

After the user is created, we can proceed with assigning permissions and privileges.

Grant permissions using T-SQL

Permissions and privileges control the access to SQL Server data and database objects. You can grant user privileges on different database objects in SQL Server.

Privileges can be of two types:

  • System privileges that allow users to create, alter or drop database objects.
  • Object privileges that allow users to execute, select, insert, update, or delete data on database objects to which the privileges were assigned.

It should be noted that only database administrators or owners of database objects can provide or revoke privileges.

The GRANT statement provides access and permissions on database objects to the user. The basic syntax is as follows:

GRANT privileges 
  ON database_name.object
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION]; 

The table describes the arguments used in the GRANT statements.

Argument Description
privileges Permissions you want to grant. They include the SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL statements.
database_name Name of the database to which the database object belongs.
object Database object on which the specified privileges will be assigned.
user_name Name of the user whom the privileges will be granted.
PUBLIC Used to grant permissions to all users.
role_name Set of privileges grouped in one category.
WITH GRANT OPTION Used to grant permissions to other users.

For example, let’s grant the SELECT, INSERT, and ALTER privileges on the HumanResources.Employee table to the user Manager that we created in the previous section.

GRANT SELECT, INSERT, ALTER ON HumanResources.Employee TO Manager;

Now, let’s view that the list of permissions on the HumanResources.Employee table includes the permissions we assigned to the user Manager. For this, we will use the system view – fn_my_permissions – and execute the following command:

EXECUTE AS USER = 'Manager';
GO
USE AdventureWorks2019
GO
SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
GO

The output is as follows:

List the permissions for the user

Revoke all privileges using T-SQL

If you want to remove the privileges on the database object from the user, you can use the REVOKE command.

REVOKE privileges
ON object
FROM {user_name |PUBLIC |role_name}

You can replace privileges with the values such as SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.

To move on, remove the INSERT and ALTER permissions on the HumanResources.Employee table assigned to the user Manager by running the following command:

REVOKE INSERT, ALTER ON HumanResources.Employee TO Manager;

To check the result, execute the following command using the fn_my_permissions view:

EXECUTE AS USER = 'Manager';
GO
USE AdventureWorks2019
GO
SELECT * FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
GO

As you can see, the INSERT and ALTER permissions were removed. Only the SELECT permission is still assigned.

Revoke privileges from the user

Deny permissions using T-SQL

The DENY permission blocks access to the database object. In case, the object has the GRANT and DENY permissions, the DENY permissions will prevail over the GRANT ones. For example, execute the following command:

DENY INSERT ON HumanResources.Employee TO Manager;

In this case, the INSERT permission will be blocked for the Manager user.

Assign roles in the SQL Server

What if you need to apply privileges to a group of users rather than to a single user? In this case, it would be better to define the role, a set of privileges and permissions. Thus, a user who is assigned to the role can access and manipulate the database objects with the same permissions as the role has.

It should be noted that to manipulate the role, you should have the ALTER permission on the role, ALTER ANY ROLE permission at a database level, and membership in the db_securityadmin fixed database role.

First, create the role with the CREATE ROLE statement:

CREATE ROLE role_name;

where role_name is the name of the role you want to create.

For example, we want to create the role Managers:

CREATE ROLE managers;

Since the role does not have any assigned privileges yet, the next step will be to add privileges to the role. For this, the GRANT command should be executed that can assign privileges to roles on databases and database objects.

For example, apply the SELECT, UPDATE, ALTER, INSERT, DELETE privileges on the Person.Address table to the role Managers.

GRANT SELECT, UPDATE, ALTER, INSERT, DELETE ON Person.Address TO managers;

After that, add users to the role by running the ALTER ROLE statement.

It adds or removes users to or from a database role, or can be used to change the name of the role.

The syntax is as follows:

ALTER ROLE  role_name  
ADD MEMBER user_name;

where

role_name is the name of the role you want to modify or drop.

user_name is the name of the existing user you want to add to the role.

If you want to remove the user from the role, use the ALTER ROLE statement with the following properties:

ALTER ROLE role_name DROP MEMBER user_name;

Note: user_name cannot be a fixed database role or a server principal.

To change the name of the role, run the following statement:

ALTER ROLE role_name WITH NAME = new_name;

where new_name is the new name of the role. Keep in mind that the name of a fixed database role cannot be modified.

Create a user in dbForge Studio for SQL Server

Now, we are going to see how comfortable it is to deal with users, roles, and privileges using a powerful SQL Server administration tool, Security Manager, available in dbForge Studio for SQL Server.

Security Manager is a reliable tool that ensures a secure and efficient way to handle access to database objects and data, to create and manipulate users and roles, as well as to grant and revoke privileges and permissions.

To get started, open Security Manager by using one of the following ways:

  • On the Start Page, switch to the Administration tab and click Manager Server Security.
  • On the Database menu, select Security Manager.

In the Security Manager, select Create User from the drop-down list. On the General tab, enter the user accounts parameters and click Save.

Create a new user using dbForge Studio for SQL Server

Assign permissions in dbForge Studio for SQL Server

To grant permissions for the user, switch to the Object Permissions tab. In the Objects block, select the database object on which you want to grant privileges. In the Available Privileges block, select the permissions to be assigned and click Save.

Grant privileges to the user in Security Manager

If you need to revoke some changes, select the privilege, click Revoke Selected Privilege, and then click Save. To deny the permission, select the checkbox next to the corresponding privilege.

Alternatively, you can remove all the granted privileges by clicking Revoke All Privileges.

Revoke privileges on database objects in dbForge Studio for SQL Server

Check user and login permissions in dbForge Studio for SQL Server

To get a list of object-level permissions assigned to the user, in Security Manager, select the user and switch to the Object Permissions tab. Then, select the object and view the available permissions.

Get a list of permissions available for the user in dbForge Studio for SQL Server

In addition, you can check the list of permissions granted to the login at the server level. For this, in Security Manager, select the login for which you want to view the permissions and switch to the Server Permissions tab. There will be the selected checkbox next to the assigned permissions in the Granted column.

Check the list of server-level permissions for the selected login in dbForge Studio for SQL Server

Conclusion

In the article, we have described how to create a SQL Server user account on database objects, and assign or revoke privileges applied to the user using T-SQL and dbForge Studio for SQL Server. As you can see, with Security Manager available in dbForge Studio for SQL Server, it is much faster to perform these tasks with several clicks, thus, saving your time and increasing productivity.

To evaluate other excellent features and capabilities dbForge Studio for SQL Server provides, download a 30-day free trial version of the tool. After it expires, you feel like purchasing a full version of dbForge Studio for SQL Server – no doubt!

Download a 30-day free trial version of dbForge Studio for SQL Server

Comments are closed.