T-SQL DCL statements in SQL Server

T-SQL DCL (Data Control Language)

This article shows how to use DCL (Data Control Language) - Permissions statements.
The T-SQL DCL statements are used to grant and revoke permissions SQL Server database.
DCL statements consist of the following T-SQL statements: GRANT, REVOKE, DENY.

GRANT

The T-SQL GRANT statement is used to grant privileges or permissions on database objects.


				GO  
				
GRANT SELECT view_name TO user_name;
				
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO user_name;
				
GRANT SELECT ON SCHEMA::schema_name TO user_name;
				
GRANT EXECUTE ON OBJECT::procedure_name TO user_name; 
				
GO

REVOKE

The T-SQL REVOKE statement is used to revoke privileges or permissions on database objects.


				GO  
				
REVOKE SELECT ON table_name TO user_name;  
				
REVOKE SELECT ON view_name TO user_name;   
				
REVOKE EXECUTE ON OBJECT::procedure_name TO user_name;
				
REVOKE SELECT ON SCHEMA::schema_name TO user_name;	
				
GO

DENY

The T-SQL DENY statement is used to deny permissions on database.


				GO  
				
DENY REFERENCES TO test_user;	
				
DENY CREATE CERTIFICATE TO test_user;	
				
DENY CONTROL ON USER::guest TO test_user; 	
				
GO