Create Login, User, assign Permission: SQL Server Tutorial

Details

In this tutorial, you will learn

How to Create a Login

To create a login, Navigate to Security > Logins

In the next screen, Enter

  1. Login Name
  2. Select SQL Server authentication
  3. Enter Password
  4. Click Ok

Login is created

You can also create a login using the T-SQL command. 

CREATE LOGIN MyLogin WITH PASSWORD = '123';

How to create a User

A user is an account that you can use to access the SQL server. To create users, you can use any of the following two ways:

Create User using SQL Server Management Studio

You will be creating a user for the EDU_TSQL database.

  1. Connect to SQL Server then expand the Databases folder from the Object Explorer.
  2. Identify the database for which you need to create the user and expand it.
  3. Expand its Security folder.
  4. Right-click the Users folder then choose "New User…"

You will get the following screen,

  1. Enter desired User name
  2. Enter the Login name (created earlier)
  3. Click OK

User is created

Create User using T-SQL

You can create a new USER using the T-SQL's create user command. The command takes the following syntax: 

create user <user-name> for login <login-name>

create user gtupapers for login MyLogin

Note: That the query should be executed within the query window. If a user is already created for a Login, SQL Server will throw an error if you create a user for the same login.

Assigning Permission to a User

Permissions refer to the rules that govern the levels of access that users have on the secured SQL Server resources. SQL Server allows you to grant, revoke and deny such permissions. There are two ways to assign permissions in SQL Server:

Assign Permission Using SQL Server Management Studio

Step 1) Connect to your SQL Server instance and expand the folders from the Object Explorer as shown below. Right click on the name of the user, that is, gtupapers then choose Properties.

Step 2) In the next screen,

  1. Click the Securables option from the left.
  2. Click on Search

Step 3) In the next window,

  1. Select "All Objects belonging to the Schema."
  2. Select Schema name as "dbo"
  3. Click OK

Step 4)

  1. Identify Table you want to Grant Permission
  2. In Explicit Permission select Grant
  3. Click Okay

Step 5) The user gtupapers is granted SELECT permission on table Course.

Grant Permission using T-SQL

To grant permission to a user using T-SQL, you first select the database using the use statement. You then assign the permission to the user using the grant statement. Here is the syntax: 

use <database-name>
grant <permission-name> on <object-name> to <username\principle>

For example, the following command shows how you can grant the select permission to the user gtupapers on the object (table) named Course within the Database EDU_TSQL: 

USE EDU_TSQL
GO
Grant select on Course to gtupapers

The permission will be granted!

 

YOU MIGHT LIKE: