GRANT And REVOKE In SQL Server

Data Control Language is used to control privileges in Databases. In Data Control Language we have two commands.

• GRANT:- GRANT command is used to provide access or privileges on the database.
• REVOKE:- REVOKE command removes user access rights or privileges to the database objects.

First, we create a login with a password “123”.

CREATE LOGIN

In the object explorer expand the security → Refresh and expand the logins → LOG_TBL login is successfully created.

LOCATION OF LOGINS

Second, we create a user by using a create user command.

CREATE USER

Go to the TestDB database → Expand the security →Expand users → USER_TBL user is created.

LOCATION OF USER

After that, we connect to a SQL Server.

Select SQL Server Authentication and enter Login Name and Password.

CONNECT TO SQL SERVER

Then we connect to the new user that is LOG_TBL.

NEW USER

LOG_TBL user can not access the address table because we do not assign GRANT permission.

LOG_TAB

So we assign GRANT permission to the user.

GRANT PERMISSION

After assigning GRANT permission LOG_TAB users access the address table.

GRANT PERMISSION

Here we remove the permission by using the REVOKE command.

REVOKE COMMAND

After assigning revoke permission. Users can not access the address table.

REVOKE PERMISSION

Then we drop the user but while execution error happens.

ERROR IN DROP COMMAND

So we execute the below query to get session id.

SESSIONS

After we get a session-id kill that session-id with the help of KILL command.

KILL COMMAND

Then we execute drop command and it successfully completed.

DROP LOGIN

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