SQL Server Login and Permissions Setup

Aug 09, 2021

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 


      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

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


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

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


Use Events27_production
Grant Select on final_backup_tidx_sctionSponsors to DemoUser

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

    DROP LOGIN Demo;


Teena Jain

About the Author

Teena Jain

I have 3+ experience as Sql Developer. I am having knoweldege of Database Design, Data Modeling, Query Optimization,T-Sql, SSIS, HTML, Css, Jquery, Javascript.