Revokes permissions from users.
Transact-SQL Syntax
To revoke permission to access database objects:
revoke [grant option for]
{all [privileges] | permission_list}
on {table_name [column_list)]
| view_name [(column_list)]
| stored_procedure_name}
from {public | name_list | role_name}
[cascade]
To revoke permission to create database objects:
revoke {all [privileges] | command_list}
from {public | name_list | role_name}
ODBC Syntax
REVOKE {ALL|revoke_privilege[,revoke_privilege]...}
ON table_name
FROM {PUBLIC|user_name[,user_name]...}
[CASCADE|RESTRICT]
revoke_privilege::= DELETE |INSERT |SELECT |UPDATE |REFERENCES
This statement revokes authorization from users.
specifies that all privileges applicable to the specified object are revoked when used to revoke authorizations to access database objects (first syntax format).
is all users of the “public” group, which includes all system users.
prohibits the users specified in name_list from granting the privileges specified by permission_list to other users.
revokes grant authorization for the privileges specified in permission_list from the users specified in name_list and from all users to whom they granted privileges.
The cascading effect occurs even if it is not specified by the user. For example, suppose UserA has granted UserB privileges, and in turn, UserB granted privileges to UserC. If UserA is revoked, all privileges that UserA granted to UserB and UserB indirectly granted to UserC are revoked.
is a list of authorizations to be revoked.
is a list of commands for which authorizations are to be revoked.
is the name of a table in the database.
is a list of columns, separated by commas, to which the privileges apply. If columns are specified, only select and update authorizations can be revoked.
is the name of a view in the current database. Only one view can be listed for each revoke statement.
is the name of a stored procedure in the database. Only one object can be listed for each revoke statement.
is a list of user database names and group names, separated by commas.
is the name of an ASE role. This allows you to revoke from all users who have been granted a specific role.
revoke insert, delete on titles from mary, sales
revoke update on titles (price, advance) from public
revoke create database, create table from mary, john
revoke execute on new_sproc from oper_role
Valid permissions for T-SQL are:
select
insert
delete
update
references
ODBC does not support revoking a stored procedure.
Authorizations can be revoked only on objects in the current database.
grant and revoke commands are order-sensitive. When a conflict occurs, the most recently issued command takes effect.
to can be substituted for from in the revoke syntax.
