MS SQL Server - Assign Permissions

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 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 object called 'TestTable' in 'TestDB' database, run the following query.

USE TestDB
GO
Grant select on TestTable to TestUser

Method 2 – Using SSMS (SQL Server Management Studio)

Step 1 − Connect to instance and expand folders as shown in the following snapshot.

Step 2 − Right-click on TestUser and click Properties. The following screen appears.

Step 3 Click Search and select specific options. Click Object types, select tables and click browse. Select 'TestTable' and click OK. The following screen appears.

Step 4 Select checkbox for Grant column under Select permission and click OK as shown in the above snapshot.

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