Assigning Statement Permissions

You can use Transact-SQL or SQL Server Enterprise Manager to grant, revoke,
and deny statement permissions.

The GRANT Statement Permission Command

The GRANT command gives a user statement permissions:

GRANT {ALL | statement_list} TO {account}

In this syntax,

ALL stands for all possible statement permissions.

statement_list is an enumerated list of the statement
permissions you want to give to an account.

account is the name of a database user, database role,
Windows user, or Windows group.

The REVOKE Statement Permission Command

The REVOKE command takes away statement permissions already granted:

REVOKE {ALL | statement_list} TO {account}

In this syntax,

ALL stands for all possible statement permissions.

statement_list is an enumerated list of the statement
permissions you want to take away.

account is the name of a database user, database role,
Windows user, or Windows group.

The DENY Statement Permission Command

Unlike a REVOKE command, DENY explicitly takes away a statement
permission. The permission doesn't have to first be granted to a user. For example,
if Joe is a member of a database role, and that role has the CREATE TABLE statement
permission, Joe can also create tables. However, if you don't want Joe to be
able to create tables, even though he is a member of a role that has the permission,
you can deny the statement permission from Joe. Therefore, Joe can't run the
CREATE TABLE statement, even though his role would normally give him
the right to do so.

DENY {ALL | statement_list} TO {account}

In this syntax,

ALL stands for all possible statement permissions.

statement_list is an enumerated list of the statement
permissions you want to deny from an account.

account is the name of a database user, database role,
Windows user, or Windows group.

Transact-SQL Permissions Examples

Working through a few examples is the easiest way to understand how to use
these commands:

To grant a windows user named Joe permission to create a view in a database,
run

GRANT CREATE VIEW TO [Rhome\Joe]

To revoke the permission to create views and tables from Joe and Mary,
run

REVOKE CREATE TABLE, CREATE VIEW FROM [Rhome\Mary], [Rhome\Joe]

To grant Joe all permissions in a database, run

GRANT ALL TO [Rhome\Joe]

NOTE

If GRANT ALL is executed in the master database, the user
specified is given all permissions in that database. If it's executed in any
other database, the user is given all permissions except CREATE DATABASE because
that particular permission can be granted only in the master database.

Assuming that user Bob is a member of Role1 and Role2, what would the permissions
be at the end of this set of statements?

At this point, Bob can create a default, and that's all. His CREATE TABLE permissions
(given to Role1) were later taken away by the REVOKE ALL FROM Role1
command. The CREATE VIEW permissions gained from Bob's membership in Role2 were
lost when Bob was denied CREATE VIEW permission. Therefore, the only permission
still in effect is the CREATE DEFAULT permission.