Requires the CASCADE option to revoke a permission granted with the WITH GRANT OPTION option.
For additional syntax information for the REVOKE statement, see the Microsoft SQL Server Transact-SQL Reference.
To revoke permission to database objects:
REVOKE [GRANT OPTION FOR]
{ALL [PRIVILEGES] | permission_list } [(column_list)]
ON { table_name [(column_list)]
| view_name [(column_list)]
| stored_procedure_name | extended_stored_procedure_name}
FROM {PUBLIC | name_list}
[CASCADE]
To revoke permission to create database objects:
REVOKE {ALL | statement_list}
FROM {PUBLIC | name_list}
where
You must use the CASCADE option when revoking a permission that is grantable. By using the CASCADE option, not only is the specified user's permission revoked, permission is revoked for all those to whom the specified user granted permission. If the CASCADE option is not specified and the specified user was granted WITH GRANT OPTION permission, an error is returned.
For example, suppose the owner of the table population_stats grants access permissions to a user, Matthew, by using WITH GRANT OPTION. The table owner enters the following statement:
GRANT SELECT ON population_stats TO matthew WITH GRANT OPTION
This gives the user Matthew permission to grant the same access privileges to another user, Jake. Matthew enters the following statement:
GRANT SELECT ON population_stats TO jake
If the owner attempts to revoke Matthew's SELECT permission with the following statement the owner receives an error:
REVOKE SELECT ON population_stats FROM matthew
The owner cannot revoke Matthew's access privilege because users would have access to tables of which the owner is not aware.
However, if the owner revokes Matthew's privileges by using CASCADE, then Matthew and Jake both lose their access to population_stats.
REVOKE SELECT ON population_stats FROM matthew WITH CASCADE
When the owner revokes Matthew's WITH GRANT OPTION privilege, then only the right to grant access privilege to other users is revoked. Users who have been granted access privileges, such as Jake, lose their access privilege. Matthew still has access privilege on the population_stats table:
REVOKE GRANT OPTION FOR SELECT ON population_stats FROM matthew
For more information about the REVOKE statement, see the Microsoft SQL Server Transact-SQL Reference.
This example revokes the SELECT and UPDATE permissions on the population_stats table for user jake.
REVOKE SELECT, UPDATE(city) ON population_stats FROM jake