How to create Login, User, and Assign Permissions in SQL Server?

1. To create a login SQL server, Navigate to Security > Logins

2. In the next screen, Enter

   a. Login Name

   b. Select SQL Server authentication

   c. Enter Password for MS SQL create a user with a password

You can also create a login using the T-SQL command for SQL server create login and user.

3. Give Full Access for Demo Login

Login is created

If we refresh the Logins, then we can view Login.

How To Create a User?

You can use any of the following two ways:

     · Using T-SQL

     · Using SQL Server Management Studio

Providing limited access only to a certain Database

You will be creating a user for the Events27_production database.

  1. Connect to SQL server to create a new user

      a. Connect to SQL Server then expand the Databases folder from the Object 

        Explorer.

      b. Identify the database for which you need to create the user and expand it.

      c. Expand its Security folder.

      d. Right-click the Users folder then choose "New User…" 

2. Enter User details, you will get the following screen,

     a. Enter the desired Username

     b. Enter the Login name (created earlier)

 User is created for that specific Database.

Create User using T-SQL

Assigning limited permission to a user in SQL Server

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 give SQL server user permissions:

 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  

2. In the next screen,

    a. Click the Securable option from the left.

    b. Click on Search

3. In the next window,

    a. Select "All Objects belonging to the Schema."

    b. Select Schema name as "dbo"

4. Grant or Revoke permission of a specific table or DB object

      a. Identify Table you want to Grant Permission

      b. In Explicit Permission select Grant

The user DemoUser is granted SELECT permission on final_backup_tidx_sctionSponsors.

Grant Permissions using T-SQL

5. Providing ROLE to a specific user:

    a. In the object explorer expand the databases and security folder.

    b. Expand Roles and right-click on Database Role.

    c. Click on New database role. Then a new pop-up window is open.

    d. In the General tab enter the role name and click on ok. 

6. Refresh the roles. In below screenshot shows the role

Remove Login from SQL Server:

   1. To drop login SQL server, Navigate to Security > Logins

   2. Select the desired login and click on Delete

Drop Login using T-SQL