REVOKE statement

Description

Removes permissions for specified users.

Syntax

Syntax 1

REVOKE
{ CONNECT | DBA | INTEGRATED LOGIN | GROUP
| MEMBERSHIP IN GROUP userid [, ...] | RESOURCE }
... FROM userid [, ...]

Syntax 2

REVOKE
{ ALL [PRIVILEGES] | ALTER | DELETE | INSERT
| REFERENCE | SELECT [ ( column-name [, ...] ) ] | UPDATE [ ( column-name,... ) ] }
... ON [ owner.]table-name FROM userid [, ...]

Syntax 3

REVOKE EXECUTE ON [ owner.]procedure-name FROM userid [, ...]

Examples

Example 1

Prevents user “dave” from inserting into the employee table:
```
REVOKE INSERT ON employee FROM dave ;
```

Example 2

Revokes resource permission from user “Jim”:
```
REVOKE RESOURCE FROM Jim ;
```

Example 3

Prevents user “dave” from updating the employee table:
```
REVOKE UPDATE ON employee FROM dave ;
```

Example 4

Revokes integrated login mapping from the user profile name “Administrator”:
```
REVOKE INTEGRATED LOGIN FROM Administrator ;
```

Example 5

Disallows the finance group from executing the procedure sp_customer_list:
```
REVOKE EXECUTE ON sp_customer_list
FROM finance ;
```

Example 6

Drops user ID “FranW” from the database:
```
REVOKE CONNECT FROM FranW ;
```

Usage

The REVOKE statement is used to remove permissions that were given using the GRANT statement. Syntax 1 is used to revoke special user permissions and Syntax 2 is used to revoke table permissions. Syntax 3 is used to revoke permission to execute a procedure. REVOKE CONNECT is used to remove a user ID from a database.

If Login Management is enabled for the database, you must use system procedures, not GRANT and REVOKE, to add and remove user IDs.

REVOKE GROUP automatically revokes membership from all members of the group.

You cannot revoke a user’s connect privileges if that user owns database objects, such as tables. Attempting to do so with a REVOKE statement or sp_dropuser procedure returns an error such as “Cannot drop a user that owns tables in runtime system.”

Automatic commit.

Standards

Permissions

Must be the grantor of the permissions that are being revoked, or must have DBA authority.

If revoking CONNECT permissions or revoking table permissions from another user, the other user must not be connected to the database.