Grant Command in SQL with Example – SQL Server Tutorial

GRANT COMMAND :–   

Grant command is used to be permission on a table to users.

Syntax:-

Grant <privilages> on <tabname> To <users> [with grant option]

The privileges are: Select, insert, update, delete.

Grant select,insert on emp to NIT. (This command is dbo is give permission to NIT table for select & insert)

go to NIT table                     

Select * from emp

  • user         user
  •  dbo            nit 
  •  granted        granted

Grant select on emp to NIT,X,Y -> This command is grant to more tables.

WITH  GRANT OPTION  :-

If  permissions are granted with grant option then grantee can also grant permission to other users.

Example :-   grant  select, insert on emp to nit with grant option.

User                 user           user      

 dbo                  nit                X

    ↓                    ↑                  ↑           → with grant option

——–emp—————emp—-

           Table                     (select,insert)

      (select,insert)

REVOKE COMMAND  :-

Revoke command is used to take back the permission from user.

Syntax:-

Revoke <privilage> on <tabname>from <users>

In dbo user 

Example :-  revoke select,insert on emp from nit cascade

Note :-  

When you give the with grant option,you must specify the cascade.

When you give the grant option only,you mentioned only revoke.

Revoke can be used to particular privileges take back the permission from user.

                           ↓

Revoke      insert  on  emp     from      nit

  Dbo                              NIT

  Emp                              emp

   ↑                                    ↑

   ———-emp————–  
                               ↓

If  you want to see the date in NIT table

                               ↓

Select * from dbo_emp

Related Projects

Leave a Reply

Your email address will not be published. Required fields are marked *