revoke

Revokes permissions from users, roles, or groups.

Syntax

Revokes 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 | function_name
		| keyname}
	[with {pred_name | {all |no} predicates}]
	from {public | name_list | role_list}
	[cascade]
	[granted by grantor]
Revokes permission to select built-in functions:
revoke select 
	on [builtin] builtin 
	from {name_list | role_list}
	[granted by grantor]
Revokes system privileges:
revoke {all [privileges] | privilege_list} 
	from {public | name_list | role_list}
	[granted by grantor]
Revokes permission to run set proxy
revoke set proxy 
	from {public | name_list | role_list}
	[granted by grantor]
Revokes dbcc privileges:
revoke {dbcc_privilege [on database]
		[, dbcc_privilege [on database], ...]} 
	from {user_list | role_list}
	[granted by grantor]
Revokes the default permissions from public:
revoke default permissions on system tables

Parameters

Examples

Usage

See also:
  • proc_role in Reference Manual: Building Blocks

  • sp_activeroles, sp_adduser, sp_changedbowner, sp_changegroup, sp_displaylogin, sp_displayroles, sp_dropgroup, sp_dropuser, sp_helpgroup, sp_helprotect, sp_helpuser in Reference Manual: Procedures

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for revoke differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, in general, revoke can be executed by a user with one of the following privilege management privileges, depending the privilege or permission being revoked.

For server-wide privileges, you must be a user with manage server permissions privilege or manage security permissions privilege.

For database-wide privileges, you must be a user with manage database permissions privilege.

For object privileges, you must be the object owner or be a user with manage any object permission privilege.

To execute revoke default, you must be the database owner or a user with own database privilege on the database.

Disabled
With granular permissions enabled:
  • Command execution – only a system administrator can revoke create database permission, and only from the master database. Only a system security officer can revoke create trigger and create encryption key permission.

  • Database consistency checking – only system administrators can run revoke dbcc commands. Database owners cannot run revoke dbcc.

  • Database object access – revoke permission for database objects defaults to object owners. An object owner can revoke permission from other users on his or her own database objects.

  • Functions – only system administrators can revoke permissions on built-in functions.

  • Proxy and session authorization – only a system security officer can revoke set proxy or set session authorization, and only from the master database.

  • Roles – you can revoke roles only from the master database. Only a system security officer can revoke sso_role, , or a user-defined role from a user or a role. Only system administrators can revoke oper_rolesa_role from a user or a role. Only a user who has both sa_role and sso_role can revoke a role that includes sa_role.

  • Tables – database owners can revoke default permissions on system tables. Table owners and the system security officer can revoke decrypt permission on a table or a list of columns in a table.

  • Defaults – database owners and logins with sa_role can revoke defaults.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

47

Audit option

revoke

Command or access audited

revoke

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – Full command text of the revoke statement

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

Related reference
grant
setuser
set