Understanding REVOKE

This is one of the topics I was planning to write for a long time, but failed due to various reasons (and without valid reasons) So, this may sound too basic for some people; if so, please forgive me. I have seen much confusion over REVOKE statement. Some believe that revoke is a different word for DENY. As explaining the security model is worth a series of articles, in this post, I am going to explain only about REVOKE statement. For this, I am using AdventureWorks database. I created two logins which I am using throughout the post

CREATE LOGIN [Sam] WITH PASSWORD=N'MyPassw0rd!_', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [Joe] WITH PASSWORD=N'MyPassw0rd!_', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Additionally I am adding these users to AdventureWorks. Two database roles also created for this purpose.

USE [AdventureWorks]

CREATE USER [Joe] FOR LOGIN [Joe] WITH DEFAULT_SCHEMA=[dbo]

CREATE USER [Sam] FOR LOGIN [Sam] WITH DEFAULT_SCHEMA=[dbo]

CREATE ROLE [Sales] AUTHORIZATION [dbo]

EXEC sp_addrolemember N'Sales', N'Joe'

EXEC sp_addrolemember N'Sales', N'Sam'

CREATE ROLE [Marketing] AUTHORIZATION [dbo]

EXEC sp_addrolemember N'Marketing', N'Sam'

To understand revoke, we need to understand that GRANT and DENY are two permissions, which a user or role can have. There are few rules related to these permissions

• GRANT and DENY are mutually exclusive permissions. That means, a user or role cannot have both GRANT and DENY permissions working. If both statements are issued against a user/role directly, the latest statement will stand as valid. (However, they could be given indirectly; the next rule explains it further.)

GRANT EXECUTE ON [dbo].[uspLogError] TO [Joe]

DENY EXECUTE ON [dbo].[uspLogError] TO [Joe]

• DENY has higher priority over GRANT. That means, when a person gets both GRANT and DENY permissions, DENY will be in effect. (There are exceptions and mechanisms to overcome them. But it does not happen at the same level) For example, use Joe could be a member of sales role. Joe may have GRANT permission to perform select operation over a table where, the sales role may have deny permission over the same table. In this case Joe will not have read permission as DENY has higher priority. Similarly, if Joe has a DENY permission, even if his group has GRANT permission, DENY permission will work against him.

GRANT EXECUTE ON [dbo].[uspLogError] TO [Sales]

The above statement, grants execute permission to sales role. As Joe and Sam are members of Sales role, they should get access to execute the procedure. But Joe has explicit DENY permission and it takes priority over GRANT permission. That makes only Sam having permission to execute the procedure.
REVOKE is not a permission. Rather it is a command which removes the permission the user/role already has. Assume that you execute the next statement.

REVOKE EXECUTE ON [dbo].[uspLogError] TO [Joe]

As the last statement was REVOKE, it will remove the permission given at the previous statement. As Joe was given DENY rights before it will be removed. It does not mean that he has GRANT permission now. The state is no permission is set for Joe on Execution of uspLogError.
Now Joe is also a member of Sales role. So he can execute the procedure in his capacity of member of sales role.
Now consider this example:

GRANT EXECUTE ON [dbo].[uspLogError] TO [Marketing]

The first statement, grants execute permission to Marketing role.

REVOKE EXECUTE ON [dbo].[uspLogError] TO [Sales]

Now the GRANT Permission is removed from sales role. That means, all its members will have permission if they have direct or indirect permission only. This will make Joe losing the execute permission. But Sam will still have permission as he is part of marketing role.

I hope this explains REVOKE statement.