revoke

Description

Revokes permissions or roles from users, groups, or roles.

Syntax

To revoke permission to access database objects:

revoke [grant option for] 
	{all [privileges] | permission_list} 
	on {table_name [(column_list)] 
		| view_name [(column_list)] 
		| stored_procedure_name}
		| keyname}
	from {public | name_list | role_list}
	[cascade]

To revoke permission to select built-in functions:

revoke select 
	on [builtin] builtin 
	to {name_list | role_list}

To revoke permission to create database objects, execute set proxy, or execute set session authorization:

revoke {all [privileges] | command_list} 
	from {public | name_list | role_list}

To revoke permission to run set proxy or set tracing:

[revoke set {proxy | tracing} 
	from {public | name_list | role_list

To revoke a role from a user or another role:

revoke role {role_name [, role_list ...]} from 
	{grantee [, grantee ...]}

To revoke access on some dbcc commands:

revoke dbcc {dbcc_command [on {all | database}]
		[, dbcc_command [on {all | database}], ...]} 
	from {user_list | role_list}

To revoke permission from other users, groups, and roles to create encryption keys.

revoke create encryption key from user | role | group

To revoke decrypt permission on a table or a list of columns in a table:

revoke decrypt on [owner.] tablename[(columnname [{,columname}])] 
	from user | group | role

To revoke the default permissions from public:

revoke default permissions on system tables

Parameters

• revoke grant option for revokes the user’s ability to grant the specified permission to other users, but does not revoke the permission itself from that user. If the user has granted that permission to others, you must use the cascade option; otherwise, you receive an error message and the revoke fails.

  For example, say you revoke the with grant option permissions from the user Bob on titles, with this statement:

  revoke grant option for select
  on titles
  from bob
  cascade
  

  • If Bob has not granted this permission to other users, this command revokes his ability to do so, but he retains select permission on the titles table.

  • If Bob has granted this permission to other users, you must use the cascade option. If you do not, you receive an error message and the revoke fails. cascade revokes this select permission from all users to whom Bob has granted it, as well as their ability to grant it to others.

• You cannot use revoke with the cascade option to revoke privileges granted by the table owner. For example, the owner of a table (UserA) can grant privileges to another user (UserB) as in this scenario:

  create table T1 (...)
  grant select on T1 to UserB
  

  However, the System Administrator cannot revoke UserB’s privileges using the revoke privileges command with the cascade option as in this statement:

  revoke select on T1 from UserA cascade
  

  This statement revokes the select privileges of the table owner, but does not revoke those privileges from UserB.

  By default, all data manipulation language (DML) operations are revoked implicitly for users other than the table owner. Because the sysprotects table contains no records indicating that the table owner has granted and then revoked privileges, the cascade option is not invoked.You must revoke explicitly the select privilege from UserB.

• To revoke set proxy or set session authorization permission, or to revoke roles, you must be a System Security Officer, and you must be in the master database.

• set proxy and set session authorization are identical, with one exception: set session authorization follows the SQL standard. If you are concerned about using only SQL standard commands and syntax, use set session authorization.

• revoke all does not include set proxy or set session authorization permissions.

• Permissions granted to roles override permissions granted to individual users or groups. Therefore, if you revoke a permission from a user who has been granted a role, and the role has that same permission, the user retains it. For example, say John has been granted the System Security Officer role, and sso_role has been granted permission on the sales table. If John’s individual permission on sales is revoked, he can still access sales because his role permissions override his individual permissions.

• Revoking a specific permission from “public” or from a group also revokes it from users who were individually granted the permission.

• Database user groups allow you to grant or revoke permissions to more than one user at a time. A user is always a member of the default group, “public” and can be a member of only one other group. The Adaptive Server installation script assigns a set of permissions to “public.”

  Create groups with sp_addgroup and remove groups with sp_dropgroup. Add new users to a group with sp_adduser. Change a user’s group membership with sp_changegroup. To display the members of a group, use sp_helpgroup.

The on database parameter specifies the database on which to invoke the database-level revoke dbcc command. Because on master revokes the ability to use dbcc commands on all databases, on master is the same as on all. You must be in the master database to use either the on all and on master parameters.

Neither the on database nor on all parameters work when invoking a server-level revoke dbcc command such as dbcc tune, because by doing so, you are forcing a server-level command to restrict itself to individual databases. For this reason, using the server-level revoke dbcc tune on master command raises an error.

default permissions on system tables revokes sysobjects (audflags) permissions from “public.”

Adaptive Server allows you to revoke permissions for users, roles, and groups for the update statistics, delete statistics, and truncate table commands. Table owners can also provide permissions through an implicit grant by adding update statistics, delete statistics, and truncate table to a stored procedure and then granting execute permissions on that procedure to a user or role.

You cannot revoke permissions for update statistics at the column level. You must have the sso_role to run update statistics or delete statistics on sysroles, syssrvroles, and sysloginroles security tables.

By default, users with the sa_role have permission to run update statistics and delete statistics on system tables other than sysroles, syssrvroles, and sysloginroles, and can transfer this privilege to other users.

You can also issue grant all to grant permissions on update statistics, delete statistics, and truncate table.

revoke update statistics on authors to billy

You cannot revoke variants of update statistics (for example, update index statistics) separately. That is, you cannot issue:

revoke update all statistics from harry

revoke fails if you attempt to revoke permissions from user-defined roles in a local temporary database.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Auditing

Values in event and extrainfo columns of sysaudits are:

See also