REVOKE Statement (version 6.5)

Requires the CASCADE option to revoke a permission granted with the WITH GRANT OPTION option.

For additional syntax information for the REVOKE statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

To revoke permission to database objects:

REVOKE [GRANT OPTION FOR]
    {ALL [PRIVILEGES] | permission_list } [(column_list)]
    ON { table_name [(column_list)]
    | view_name [(column_list)]
    | stored_procedure_name | extended_stored_procedure_name}
    FROM {PUBLIC | name_list}
    [CASCADE]

To revoke permission to create database objects:

REVOKE {ALL | statement_list}
    FROM {PUBLIC | name_list}

where

GRANT OPTION FOR

Revokes the ability to confer the permission to another user but leaves the user with the permission still granted.

ALL

Specifies that all permissions possessed by all the users in the group or name_list to the specified object be revoked. When used to revoke statement permissions, specifies that all statement permissions possessed by the user be revoked.

permission_list =
{SELECT [(column_list)] | UPDATE [(column_list)]
| REFERENCES | DELETE | INSERT}

[, {SELECT [(column_list)] | UPDATE [(column_list)] | REFERENCES
| DELETE | INSERT} ...]

Lists permissions to revoke.

table_name

Is the name of the table in the current database to which the permissions apply.

column_list

Is a list of column names, delimited by commas, to which the permissions apply. If columns are specified, only SELECT and UPDATE permissions can be revoked.

view_name

Is the name of the view in the current database to which the permissions apply.

stored_procedure_name

Is the name of a stored procedure in the current database to which permissions apply.

extended_stored_procedure_name

Is the name of an extended stored procedure in the current database to which the permissions apply.

PUBLIC

Represents all users, including those who are members of other groups.

name_list

Is a list of user names and/or group names, delimited by commas. These names are stored in the database's sysusers table.

CASCADE

Revokes WITH GRANT privileges that were granted by the specified user, including those of the specified user. The specified user is either a name_list or PUBLIC.

statement_list

Is a list of statements that have already been granted. The statement list can include: CREATE DATABASE, CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, DUMP DATABASE, and DUMP TRANSACTION.

Remarks

You must use the CASCADE option when revoking a permission that is grantable. By using the CASCADE option, not only is the specified user's permission revoked, permission is revoked for all those to whom the specified user granted permission. If the CASCADE option is not specified and the specified user was granted WITH GRANT OPTION permission, an error is returned.

For example, suppose the owner of the table population_stats grants access permissions to a user, Matthew, by using WITH GRANT OPTION. The table owner enters the following statement:

GRANT SELECT ON population_stats TO matthew WITH GRANT OPTION
  

This gives the user Matthew permission to grant the same access privileges to another user, Jake. Matthew enters the following statement:

GRANT SELECT ON population_stats TO jake 
  

If the owner attempts to revoke Matthew's SELECT permission with the following statement the owner receives an error:

REVOKE SELECT ON population_stats FROM matthew
  

The owner cannot revoke Matthew's access privilege because users would have access to tables of which the owner is not aware.

However, if the owner revokes Matthew's privileges by using CASCADE, then Matthew and Jake both lose their access to population_stats.

REVOKE SELECT ON population_stats FROM matthew WITH CASCADE
  

When the owner revokes Matthew's WITH GRANT OPTION privilege, then only the right to grant access privilege to other users is revoked. Users who have been granted access privileges, such as Jake, lose their access privilege. Matthew still has access privilege on the population_stats table:

REVOKE GRANT OPTION FOR SELECT ON population_stats FROM matthew
  

For more information about the REVOKE statement, see the Microsoft SQL Server Transact-SQL Reference.

Example

This example revokes the SELECT and UPDATE permissions on the population_stats table for user jake.

REVOKE SELECT, UPDATE(city) ON population_stats FROM jake