Grant and Revoke Command in SQL Server

Introduction

In this article I described Grant and Revoke commands in SQL Server. These are Data Control Language Commands used to enforce security in a multi-user database environment.

First of all we create a table named Deep on which we enforced the Grant and Revoke commands.

Creation of table:

create table deep(userId int, UserName varchar(15))
 
Insertion of data:

insert into deep

select 1,'d'union all

select 2,'e'union all

select 3,'f'union all

select 4,'g'
 
Output:

select * from deep

Grant Command:

Grant is used to provide permissions like Select, All, Execute to user on the database objects like Tables, Views, Databases etc.

Syntax:

Grant privilageName
on objectName
To{userName/Public/roleName}
[with Grant Option]

Here privilageName is the the access right or permission that is granted to the user like All, Select, Execute. objectName is the name of a database object like Table, View or Stored Procedure. UserName is the name of the user to whom the permission is granted. "With Grant Option" allows the user to grant the permission to the other user and are optional.

Example:
 

grant select

on deep

to user24
 

Here you can give the name of your user, public or you can give the role name also. This command grants a SELECT permission on the Deep table to user24.

Output:

With Grant Option:

grant select

on deep

to user24
with grant option

You should use the WITH GRANT option carefully because if you use  the WITH GRANT option, then user24 can GRANT SELECT privilege on the Deep table to another user, such as user25 etc. Later, if you REVOKE the SELECT privilege on employee from user24, still user25 will have SELECT privilege on the employee table.

Output:
 

Revoke Command:

Revoke is used to remove the permissions or privileges provided to a user by the Grant command.

Syntax:

Revoke privilageName
on objectName
from{userName/public/roleName}

Example:

revoke select

on deep

from public

Output:

Summary

In this app I described Grant and Revoke Commands in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.