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. However, although 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, if you revoke permission for a user to view a table, you do not necessarily prevent the user from viewing the table because permission to view the table was granted to a role to which he belongs.

For example, removing SELECT access on the Employees table from the HumanResources role revokes permission so that HumanResources can no longer use the table. If HumanResources is a member of the Administration role. If you later grant SELECT permission on Employees to Administration, members of HumanResources can see the table through their membership in Administration. However, if 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

Transact-SQL