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.

CREATE A LOGIN

If we refresh the Logins then we can view Login.

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.

CREATE A USER

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

USER

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

• For assigning permission goto USER_SAMPLE property.
• Click on the search option.

SEARCH

• 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.

ADD OBJECT

• We select the table to grant permission.
• In the Explicit permission select Grant.
• Then click on ok.

GRANT PERMISSION

• 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.

CREATE A ROLE

Refresh the roles. In below screenshot shows the role.

ROLE

DROP LOGIN :

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

DROP LOGIN

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