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?
EXEC sp_addrole 'Role1' EXEC sp_addrole 'Role2' GO GRANT CREATE TABLE TO Role1 GRANT CREATE VIEW to Role2 GRANT CREATE DEFAULT to [Rhome\Bob] REVOKE ALL FROM Role1 DENY CREATE VIEW to [Rhome\Bob]
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.