SQL Server -DCL Commands (Data Control Language )

Data Control Language (DCL)

Data Control language is used to Provide/Remove access permission to users on database or entities in the database.

While creating user permissions generally DBA uses this language to give access permissions.

GRANT and REVOKE commands are used to perform DCL.

GRANT – Gives user’s access privileges to database.

Syntax:

GRANT privilege_name 
ON object_name 
TO {user_name |PUBLIC |role_name} 
[WITH GRANT OPTION]; 

Example:

GRANT SELECT/INSERT/UPDATE/TRUNCATE

ON Tbl_Employees

TO user

            

REVOKE – Withdraws user’s access privileges to the database given with the GRANT command.

REVOKE  privilege_name 
ON object_name 
TO {user_name |PUBLIC |role_name} 
[WITH GRANT OPTION]; 

REVOKE  SELECT/INSERT/UPDATE/TRUNCATE

ON Material

TO user

             Note:  Instead of the user, we can give permission on role base, Schema-based.