We use the GRANT keyword to grant permissions to securables. The basic syntax for the GRANT statement is as follows:

GRANT permission [,…n]
TO <grantee_principal> [,…n] [WITH GRANT OPTION]
[AS <grantor_principal>]

We use WITH GRANT OPTION when we want the user to grant the same permission to other logins.

For example, to grant Bob the SELECT permission WITH GRANT OPTION on the Book_Info table, you execute the following code:

USE [CH02_03];
GO

GRANT SELECT ON [dbo].[Book_Info] TO [Bob]
WITH GRANT OPTION;
GO

We use the DENY keyword to prevent a user from performing certain actions. The basic syntax for this command is as follows:

DENY permission [,…n]
TO <grantee_principal> [,…n]
[CASCADE]
[AS <grantor_principal>]

We can specify the CASCADE option when we want to deny the permission to the specified principal and to all the other principals to which the principal granted the permission.

For example, enter and execute the following T-SQL code to deny Bob the UPDATE permission on the Book_Info table:

USE [CH02_03];
GO

DENY UPDATE ON [dbo].[Book_Info] TO [Bob];
GO

We use the REVOKE keyword to remove the permission assigned using the GRANT or DENY keyword. The following is the basic syntax for this command:

REVOKE [GRANT OPTION FOR] permission [,…n]
{ TO | FROM } <grantee_principal> [,…n]
[CASCADE]
[AS <grantor_principal>