SQL Server - Assign Permissions

This blog helps in providing permissions, which refer to the rules governing the levels of access.

Permissions refer to the rules governing the levels of access that principals have to securables. You can grant, revoke and deny permissions in MS SQL Server.

To assign the permissions, either of the following two methods can be used.

Method 1 – Using T-SQL

Syntax

Use <database name>

Grant <permission name> on <object name> to <username\principle>

Example

To assign, select permission to a user called 'TestUser' on an object called 'TestTable' in 'TestDB' database, run the query given below.

USE TestDB

GO

Grant select on TestTable to TestUser

Method 2 – Using SSMS (SQL Server Management Studio)

Step 1

Connect to an instance and expand the folders, as shown below.

 

Step 2

Right-click on TestUser and click Properties. The screen given below appears.

 

Step 3

Click Search and select specific options. Click Object types, select tables and click browse. Select TestTable and click OK. The screen given below appears.

 

Step 4

Select checkbox for Grant column under select permission and click OK, as shown above.

 

Step 5

Select permission on TestTable of TestDB database granted to TestUser. Click OK.