Deactivating Established Access by Revoking Permissions

You can revoke a permission that has been granted or denied previously. Revoking is similar to denying in that both remove a granted permission at the same level. The difference is that while revoking a permission removes a granted permission, it does not prevent the user, group, or role from inheriting a granted permission from a higher level. Therefore, although a user can have permission to view a table directly revoked, it is possible that the user can still view the table because permission to view the table was granted to a role to which they belong.

For example, to remove SELECT access on the Employees table from the HumanResources role, revoke the permission so that HumanResources can no longer use the table. If HumanResources is a member of the Administration role, and you later grant SELECT permission on Employees to Administration, members of HumanResources can see the table through their membership in Administration. If, however, you deny permission to HumanResources, the permission is not inherited if later granted to Administration, because the deny permission cannot be undone by a permission at a different level.

Similarly, it is also possible to remove a previously denied permission by revoking the deny for the permission. However, if a user has other denied permissions at the group or role level, then the user still is denied access.

Note You can revoke permissions to user accounts only in the current database, for objects in the current database.

To revoke permissions on an object

To revoke statement permissions from users in a database

To revoke permissions on multiple objects from a user, group, or role