Assign Permissions to User in SQL Server

In the previous chapter, you learned to create a new user in the database. Here, you will learn to grant permissions to a user in SQL Server.

You can GRANT and REVOKE permissions on various database objects in SQL Server. User permissions are at the database level.

You can grant any or a combination of the following types of permissions:

Select: Grants user the ability to perform Select operations on the table.
Insert: Grants user the ability to perform the insert operations on the table.
Update: Grants user the ability to perform the update operations on the table.
Delete: Grants user the ability to perform the delete operations on the table.
Alter: Grants user permission to alter the table definitions.
References: References permission is needed to create a Foreign key constraint on a table. It is also needed to create a Function or View WITH SCHEMABINDING clause that references that object
Control: Grants SELECT, INSERT, UPDATE, DELETE, and REFERENCES permission to the User on the table.

Similarly, you can revoke any of the already assigned permissions.

You can GRANT or REVOKE permissions by using SSMS or by executing T-SQL Script.

Grant or Revoke permissions using T-SQL

Syntax:

            USE <database-name>
--GRANT Permissions Syntax
GRANT <permissions> ON <db-object> TO <user-name, login-name, or group>

--REVOKE Permissions Syntax
REVOKE <permissions> ON <db-object> FROM <user-name, login-name, or group>

        

The following SQL grants the SELECT permission to a user Steve on Employee table in the HR database.

            USE HR;
GRANT SELECT ON Employee TO Steve;

The following SQL grants the SELECT, INSERT, UPDATE, DELETE permissions to a user Steve on EmployeeAddress table in the HR database.

            USE HR;
GRANT SELECT, INSERT, UPDATE, DELETE ON EmployeeAddress TO Steve;

The following grants SELECT permission on the Employee table to all users by granting the privileges to the public role.

            USE HR;
GRANT SELECT ON Employee TO public;

The following revokes DELETE permission to Steve user on the EmployeeAddress table.

            USE HR;
REVOKE DELETE ON EmployeeAddress FROM Steve;

Learn more on GRANT permissions and REVOKE permissions.

Grant or Revoke permissions using SSMS

Let's grant permissions to a user using SQL Server Management Studio.

Login to SQL Server Management Studio. In Object Explorer on the left pane, expand the Databases folder and select the concerned database and navigate to the by expanding Security and Users folders. Right-click the User to which you want to GRANT or REVOKE the permissions. Here, we will GRANT permissions to our new user 'Steve'. So right-click on a user Steve and click Properties, as shown below.

This will open Database User window, as shown below. Select Securable tab on the left pane to GRANT or REVOKE permissions to database objects. Click on the Search button to add database objects to which you want to GRANT or REVOKE permissions. This will open Add Objects popup, as shown below.

On the Add Objects popup, you have three options to select database objects (such as table, view, stored procedures, etc.) to which you want to grant permissions.

Specific Objects.. option allows you to find and select specific database objects to grant or revoke permissions.
All Objects of types.. option allows you to find and select a specific type of database object to grant or revoke permissions such as table, stored procedures, views, inline functions, etc. For example, selecting table type will display all the tables in the database to grant or revoke permissions.
All Objects belonging to the schema option allows you to select a schema to grant permissions to all the database objects of that schema.

Here, we will select "All Objects belonging to the schema" radio button. Select a schema from the dropdown and click OK. This will display all the database objects under this schema, as shown below.

Now, select the table for which you have to give permissions in the upper pane. In the bottom pane, select Explicit tab and click on SELECT, UPDATE, and any other permission that you wish to grant to the user for the table. Click on the OK button. The selected permissions will be granted to the user. To revoke permission, uncheck the permission checkbox.

Thus, you can grant or revoke permissions to the specific user in SQL Server database.