Grant and Revoke Command in SQL Server

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 

create table deep(userId int, UserName varchar(15))

Insert data into a table

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

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

 

 

 

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

revoke select
on deep
from public

Output

 

 

 

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.