Grant and Revoke Command in SQL Server

In this article, I will describe Grant and Revoke Commands in SQL Server.

Introduction

 
In this article, I will describe Grant and Revoke commands in SQL Server. SQL Grant and SQL Revoke are SQL Data Control Language commands and are 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.
 
Create a table 
  1. create table deep(userId int, UserName varchar(15))
Insert data into a table
  1. insert into deep  
  2. select 1,'d'union all  
  3. select 2,'e'union all  
  4. select 3,'f'union all  
  5. select 4,'g'  
Output
  1. select * from deep
dcl-commands-in-Sql-server.jpg 
 

Grant in SQL Server

 
SQL Grant is used to provide permissions like Select, All, Execute to user on the database objects like Tables, Views, Databases and other objects in a SQL Server.
 
Syntax
 
Grant privilageName
on objectName
To{userName/Public/roleName}
[with Grant Option]
 
Here privilageName is 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
  1. grant select  
  2. on deep  
  3. 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
 
Grant in SQL Server 
 
With Grant Option
  1. grant select  
  2. on deep  
  3. to user24  
  4. 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
 
Grant in SQL Server 
 

Revoke in SQL Server

 
SQL Revoke is used to remove the permissions or privileges of a user on database objects set by the Grant command.
 
Syntax
 
Revoke privilageName
on objectName
from{userName/public/roleName}
 
Example
  1. revoke select  
  2. on deep  
  3. from public  
Output
 
Revoke in SQL Server 
 

Summary

 
In this article, 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.