revoke (core)

Syntax

Transact-SQL Syntax

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]

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.

Parameters

• all – specifies that all privileges applicable to the specified object are revoked when used to revoke authorizations to access database objects (first syntax format).
• public – is all users of the “public” group, which includes all system users.
• grant option for – prohibits the users specified in name_list from granting the privileges specified by permission_list to other users.
• cascade – 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 User A has granted User B privileges, and in turn, User B granted privileges to User C. If User A is revoked, all privileges that User A granted to User B and User B indirectly granted to User C are revoked.

• permission_list – is a list of authorizations to be revoked.
• command_list – is a list of commands for which authorizations are to be revoked.
• table_name – is the name of a table in the database.
• column_list – 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.
• view_name – is the name of a view in the current database. Only one view can be listed for each revoke statement.
• stored_procedure – is the name of a stored procedure in the database. Only one object can be listed for each revoke statement.
• name_list – is a list of user database names and group names, separated by commas.
• role_name – is the name of an Adaptive Server role. This allows you to revoke from all users who have been granted a specific role.

Examples

• Example 1 – revokes insert and delete permission on titles table for username Mary.

  revoke insert, delete
   on titles
   from mary, sales

• Example 2 – revokes update permission on titles table from all users.

  revoke update
   on titles (price, advance)
   from public

• Example 3 – revokes create database and create table permissions.

  revoke create database, create table
   from mary, john

• Example 4 – revokes execute permission from oper_role.

  revoke execute on new_sproc
  from oper_role

Usage