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
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
Employee table in the
USE HR; GRANT SELECT ON Employee TO Steve;
The following SQL grants the SELECT, INSERT, UPDATE, DELETE permissions to a user
EmployeeAddress table in the
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
USE HR; REVOKE DELETE ON EmployeeAddress FROM Steve;
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
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.