REVOKE (T-SQL)

Removes a previously granted or denied permission from a user in the current database.

Syntax

Statement permissions:

REVOKE {ALL | statement[,...n]}
FROM security_account[,...n]

Object permissions:

REVOKE [GRANT OPTION FOR]
    {ALL [PRIVILEGES] | permission[,...n]}
    {
        [(column[,...n])] ON {table | view}
        | ON {table | view}[(column[,...n])]
        | {stored_procedure | extended_procedure}
    }
{TO | FROM}
    security_account[,...n]
[CASCADE]
[AS {group | role}]

Arguments

ALL

Specifies that all applicable permissions are being removed. For statement permissions, ALL can be used only by members of the sysadmin fixed server role. For object permissions, ALL can be used by members of the sysadmin fixed server and db_owner fixed database roles, and database object owners.

statement

Is a granted statement for which permission is being removed. The statement list can include:

• CREATE DATABASE
• CREATE DEFAULT
• CREATE PROCEDURE
• CREATE RULE
• CREATE TABLE
• CREATE VIEW
• BACKUP DATABASE
• BACKUP LOG

n

Is a placeholder indicating the item can be repeated in a comma-separated list.

FROM

Specifies the security account list.

security_account

Is the security account in the current database from which the permissions are being removed. The security account can be a:

• Microsoft® SQL Server™ user.
• SQL Server role.
• Microsoft Windows NT® user.
• Windows NT group.

Permissions cannot be revoked from the system roles, such as sysadmin. When permissions are revoked from a SQL Server or Windows NT user account, the specified security_account is the only account affected by the permissions. If permissions are revoked from a SQL Server role or a Windows NT group, the permissions affect all users in the current database who are members of the group or role, unless the user has already been explicitly granted or denied a permission.

There are two special security accounts that can be used with REVOKE. Permissions revoked from the public role are applied to all users in the database. Permissions revoked from the guest user are used by all users who do not have a user account in the database.

When revoking permissions to a Windows NT local or global group, specify the domain or computer name the group is defined on, followed by a backslash, then the group name, for example London\JoeB. However, to revoke permissions to a Windows NT built-in local group, specify BUILTIN instead of the domain or computer name, for example BUILTIN\Users.

GRANT OPTION FOR

Specifies that WITH GRANT OPTION permissions are being removed. Use the GRANT OPTION FOR keywords with REVOKE to remove the effects of the WITH GRANT OPTION setting specified in the GRANT statement. The user still has the permissions, but cannot grant the permissions to other users.

If the permissions being revoked were not originally granted using the WITH GRANT OPTION setting, GRANT OPTION FOR is ignored if specified, and permissions are revoked as usual.

If the permissions being revoked were originally granted using the WITH GRANT OPTION setting, specify both the CASCADE and GRANT OPTION FOR clauses; otherwise, an error is returned.

PRIVILEGES

Is an optional keyword that can be included for SQL-92 compliance.

permission

Is an object permission that is being revoked. When permissions are revoked on a table or a view, the permission list can include one or more of these statements: SELECT, INSERT, DELETE, or UPDATE.

Object permissions revoked on a table can also include REFERENCES, and object permissions revoked on a stored procedure or extended stored procedure can be EXECUTE. When permissions are revoked on columns, the permissions list can include SELECT or UPDATE.

column

Is the name of the column in the current database for which permissions are being removed.

table

Is the name of the table in the current database for which permissions are being removed.

view

Is the name of the view in the current database for which permissions are being removed.

stored_procedure

Is the name of the stored procedure in the current database for which permissions are being removed.

extended_procedure

The name of an extended stored procedure for which permissions are being removed.

TO

Specifies the security account list.

CASCADE

Specifies that permissions are being removed from security_account as well as any other security accounts that were granted permissions by security_account. Use when revoking a grantable permission.

If the permissions being revoked were originally granted to security_account using the WITH GRANT OPTION setting, specify both the CASCADE and GRANT OPTION FOR clauses; otherwise, an error is returned. Specifying both the CASCADE and GRANT OPTION FOR clauses revokes only the permissions granted using the WITH GRANT OPTION setting from security_account, as well as any other security accounts that were granted permissions by security_account.

AS {group | role}

Specifies the optional name of the security account in the current database under whose authority the REVOKE statement will be executed. AS is used when permissions on an object are granted to a group or role, and the object permissions need to be revoked from other users. Because only a user, rather than a group or role, can execute a REVOKE statement, a specific member of the group or role revokes permissions from the object under the authority of the group or role.

Remarks

Only use REVOKE with permissions in the current database.

A revoked permission removes the granted or denied permission only at the level revoked (user, group, or role). For example, permission to view the authors table is explicitly granted to the Andrew user account, which is a member of the employees role only. If the employees role is revoked access to view the authors table, Andrew can still view the table because permission has been explicitly granted. Andrew is unable to view the authors table only if Andrew is revoked permission as well. If Andrew is never explicitly granted permissions to view authors, then revoking permission from the employees role prevents Andrew from viewing the table.

Note In Microsoft SQL Server version 7.0, REVOKE removes only previously granted or denied permissions; therefore, existing SQL Server 6.x scripts that use REVOKE may have to be changed to use DENY to maintain behavior.

If a user activates an application role, the effect of REVOKE is null for any objects the user accesses using the application role. Although a user may be revoked access to a specific object in the current database, if the application role has access to the object, the user also has access while the application role is activated.

Use sp_helprotect to report the permissions on a database object or user.

Permissions

REVOKE permissions default to members of the sysadmin fixed server role, db_owner and db_securityadmin fixed database roles, and database object owners.

Examples

A. Revoke statement permissions from a user account

This example revokes the CREATE TABLE permissions that have been granted to the users Joe and Corporate\BobJ. It removes the permissions that allow Joe and Corporate\BobJ to create a table; however, Joe and Corporate\BobJ can still create tables if CREATE TABLE permissions have been granted to any roles of which they are members.

REVOKE CREATE TABLE FROM Joe, [Corporate\BobJ]

B. Revoke multiple permissions from multiple user accounts

This example revokes multiple statement permissions from multiple users.

REVOKE CREATE TABLE, CREATE DEFAULT

FROM Mary, John

C. Revoke a denied permission

The user Mary is a member of the Budget role, which has been granted SELECT permissions on the Budget_Data table. The DENY statement has been used with Mary to prevent access to the Budget_Data table through the permissions granted to the Budget role.

This example removes the denied permission from Mary and, through the SELECT permissions applied to the Budget role, allows Mary to use the SELECT statement on the table.

REVOKE SELECT ON Budget_Data TO Mary

See Also