REVOKE Statement

For SQL Server 6.5 information, see REVOKE Statement in What's New for SQL Server 6.5.

Revokes object and statement permissions from users.

Syntax

Statement permissions:

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

Object permissions:

REVOKE {ALL | permission_list}
ON {table_name [(column_list)] | view_name [(column_list)] |
stored_procedure_name | extended_stored_procedure_name}
FROM {PUBLIC | name_list}

where

ALL: Specifies that all permissions applicable to the specified object are revoked when used to revoke object permissions. When used to revoke statement permissions specifies that all statement permissions are revoked. (CREATE DATABASE permission can be granted only by the system administrator.)
statement_list: Lists granted statements. The statement list can include CREATE DATABASE (which can be granted only by the system administrator), CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, DUMP DATABASE, and DUMP TRANSACTION. If more than one statement is listed, separate them with commas.
PUBLIC: Represents all users, including those who are members of other groups.
name_list: Lists usernames and/or group names, separated by commas.
permission_list: Lists granted permissions. When permissions are revoked on a table or a view, the permission list can include SELECT, INSERT, DELETE, UPDATE, and/or REFERENCES. When permissions are revoked on columns, the permission list can include SELECT and/or UPDATE. When permissions are revoked on stored procedures, the permission list can include only EXECUTE. If more than one permission is listed, separate them with commas.
table_name: Is the name of the table in the current database to which the permissions apply.
column_list: Lists names of columns, separated by commas, to which the permissions apply. If columns are specified, only SELECT and UPDATE permissions can be granted.
view_name: Is the name of the view in the current database to which the permissions apply.
stored_procedure_name: Is the name of the stored procedure in the current database to which the permissions apply.
extended_stored_procedure_name: Is the name of an extended stored procedure in the current database to which the permissions apply.

Remarks

When permissions are given, information is stored within the sysprotects database table. The sysprotects table manages permissions by updating rows based on each GRANT and REVOKE statement. You can grant or revoke permissions only on objects in the current database.

If permissions are granted to the group public, those permissions will override any previous permissions given to any other user or group of that database. When permissions are given (or revoked), the permissions are order-sensitive. (The statement executed most recently is the one that takes effect.) This can produce undesired results if care is not taken when granting/revoking permissions. For best results, grant/revoke permissions to the least-selective group first (usually public), and then grant/revoke permissions on a group-by-group basis, and then on an individual-by-individual basis, and then to/from the guest user.

The sp_helprotect system stored procedure reports permissions on a database object or on a user. For details, see the sp_helprotect system stored procedure.

Example

This example shows how to revoke multiple statement permissions from multiple users.

REVOKE CREATE TABLE, CREATE DEFAULT
FROM Mary, John