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.

MS SQL Server - Create Users

 User refers to an account in MS SQL Server database which is used to access database.

Users can be created using either of the following two methods.

Method 1 – Using T-SQL

Syntax

Create user <username> for login <loginname>

Example

To create user name 'TestUser' with mapping to Login name 'TestLogin' in TestDB database, run the following query.

create user TestUser for login TestLogin

Where 'TestLogin' is the login name which was created as part of the Login creation

Method 2 – Using SSMS (SQL Server Management Studio)

Note − First we have to create Login with any name before creating a user account.

Let’s use Login name called 'TestLogin'.

Step 1 − Connect SQL Server and expand databases folder. Then expand database called 'TestDB' where we are going to create the user account and expand the security folder. Right-click on users and click on the new user to see the following screen.

Step 2 − Enter 'TestUser' in the user name field and click on ellipse to select the Login name called 'TestLogin' as shown in the following snapshot.

Step 3 − Click OK to display login name. Again click OK to create 'TestUser' user as shown in the following snapshot.