Removes permissions for specified users.
Syntax 1
REVOKE
{ CONNECT | DBA | INTEGRATED LOGIN | GROUP
| KERBEROS LOGIN | 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 [, …]
Syntax 4
REVOKE CREATE ON dbspace-name FROM userid [, …]
Prevents user “dave” from inserting into the Employees table:
REVOKE INSERT ON Employees FROM dave ;
Revokes resource permission from user “Jim”:
REVOKE RESOURCE FROM Jim ;
Prevents user “dave” from updating the Employees table:
REVOKE UPDATE ON Employees FROM dave ;
Revokes integrated login mapping from the user profile name “Administrator”:
REVOKE INTEGRATED LOGIN FROM Administrator ;
Disallows the finance group from executing the procedure sp_customer_list:
REVOKE EXECUTE ON sp_customer_list FROM finance ;
Drops user ID franw from the database:
REVOKE CONNECT FROM franw ;
Revokes CREATE privilege on dbspace DspHist from user Latifah:
REVOKE CREATE ON DspHist FROM Latifah
Revokes CREATE permission on dbspace DspHist from user ID fionat from the database:
REVOKE CREATE ON DspHist FROM fionat ;
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.
REVOKE CREATE removes Create permission on the specified dbspace from the specified user IDs.
You cannot revoke permissions for a specific user within a group. If you do not want a specific user to access a particular table, view, or procedure, then do not make that user a member of a group that has permissions on that object.
You cannot revoke the connect privileges of a user 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.
SQL92 Syntax 1 is a vendor extension. Syntax 2 is an entry-level feature. Syntax 3 is a Persistent Stored Module feature.
Sybase Syntax 2 and 3 are supported by Adaptive Server Enterprise. Syntax 1 is not supported by Adaptive Server Enterprise. User management and security models are different for Sybase IQ and Adaptive Server Enterprise.
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.
For Syntax 4, you must have DBA authority.
