Introduction

Security is one of the most important aspects of a database management system. You do not want every user of the database to have access to all objects of a database. This is where SQL GRANT and SQL REVOKE statements come in play.
SQL Grant sets permissions or privileges of a user on a database objects like tables, views, and stored procedures. For example, you can set a user read and write on a database table and execute a stored procedure using SQL Grant statement. SQL REVOKE is reverse of SQL GRANT. SQL Revoke removes permissions or privileges of a user on database objects set by the Grant command. In this article, I will describe Grant and Revoke commands in SQL Server and how to use SQL Grant and SQL Revoke to set and remove permissions on database objects 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
Here is more details on SQL Grant Read Write Permissions To User 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.