DENY vs. REVOKE – Part 2 – Hierarchies of Principals

Introduction

Yesterday we asked this question: Are REVOKE and DENY just synonyms of each other?

We did see that they behave identical when looking just at a single security principal and a single securable in isolation. However, I did promise to show you that there is indeed a difference between the two if you look at security hierarchies.

To follow up on that promise, today I am going to show you an example that involves roles.

Role Member DENY Example

Before we can get started we need a database role with a member and a table:

Now we can GRANT SELECT on the table to TestRole1. That will give TestUser1 access to the table, as TestUser1 is a member of TestRole1:

This is how we expect role permissions to work. If you need a refresher on roles, check out my previous posts about database roles and server roles.

Now let's REVOKE the SELECT permission on the table from TestUser1 and see what that does to TestUser1's ability to access to table:

As you can see, TestUser1 still has access to the table. When I introduced the REVOKE statement a few days ago I wrote: "The REVOKE statement causes a previously granted identical privilege to be revoked. It works only on the same privilege on the same securable for the same grantee."

With those two sentences in mind, the above behavior is exactly as expected. The permission in question was granted to the role, so trying to revoke it from the role member should not do anything.

Now let's see what happens if we use DENY instead:

Now TestUser1 does not have access to the table anymore. So, other than a REVOKE that just can remove existing identical grants, a DENY overrules grants on different levels of the principal hierarchy.

In my post about The Secret of the Security Token I showed you that SQL Server actually looks at quite a few principals when determining the active permissions for the current request. The token DMVs return together one row for each principal involved. That includes in particular any database role that the current user is a member of. If any of those principals has been granted access to the securable, access is permitted. However, if any other database or server principal in that list has been denied access to that securable, the DENY overrides the grant and access is refused.

This means in particular, that it does not matter which of the principals was granted and which was denied. Any deny anywhere in the hierarchy overrides any number of grants on the same resource.

Summary

While REVOKE and DENY might seem similar at first glance, the DENY statement is actually a lot more powerful as it can override a GRANT that happened at a different level of the principal hierarchy.

DENY vs. REVOKE Series

This post is part of a five-part series comparing the DENY and the REVOKE statements.
Below is a list of links to the posts that are already available.