SQL-Server

SQL Server Login and Permissions Setup

Teena Jain
By Teena Jain
Aug 09, 2021
SQL Server Login and Permissions Setup

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.

   CREATE LOGIN MyLogin WITH PASSWORD = MsSQL

 

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

    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
Go
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;

 

TAGS SQL-Jobs Scheduler SQL
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.

RECENT POSTS

CATEGORIES

TAGS CLOUD