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 * from deep
Grant is used to provide permissions like Select, All, Execute to user on the database objects like Tables, Views, Databases etc.
[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.
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.
With Grant Option:
on deepto 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.
Revoke is used to remove the permissions or privileges provided to a user by the Grant command.
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.