SQL Server- Create Login, User, Role and Assign Permission.

In this article, we learn how to assign permission to a SQL Server.

  • Data Control Language Command restricts the user from accessing data in a database.
  • GRANT command is used to provide access or privileges on the database.

LOGIN:

  • In object explorer goto Security → Right click on Logins → Select New login.
  • Enter Login name
  • In SQL Server authentication enter the password.
  • Then click on ok.

If we refresh the Logins then we can view Login.

USER:

  • In object explorer expand databases.
  • Expand the database which you need to create the user.
  • Expand the security folder. Right-click on Users and select New User.
  • In the new pop up window enter the User name and Login name after that click on ok.

We refresh the users and then it has shown the created users.

ASSIGN PERMISSION: SQL Server allows to GRANT, REVOKE permissions.

  • For assigning permission goto USER_SAMPLE property.
  • Click on the search option.
  • When we click on search new pop up window is open.
  • Select All objects belonging to the schema
  • The select schema names as “dbo” and then click on ok.
  • We select the table to grant permission.
  • In the Explicit permission select Grant.
  • Then click on ok.
  • The USER_SAMPLE is granted SELECT permission on the table Table_2.

ROLE:

  • In the object explorer expand the databases and security folder.
  • Expand Roles and right-click on Database Role.
  • Click on New database role. Then a new pop up window is open.
  • In the General tab enter the role name and click on ok.

Refresh the roles. In below screenshot shows the role.

DROP LOGIN :

  • Here we drop the sample login.
  • Syntax: DROP LOGIN LOGIN_NAME

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -