DENY vs. REVOKE – Part 1 – Are they just synonyms?

Introduction

Over the last two weeks we have taken an in-depth look at the GRANT and REVOKE statements. Today I would like to introduce you to a third permission management statement: DENY

If you have worked with the DENY before, you might get the feeling this post is leading in the wrong direction. Just hang with me, I promise I won't leave you stranded.

DENY Example

At first glance the DENY statement looks very similar in functionality to the REVOKE statement: It removes an existing permission. Let's look at an example.

First we need our principal TestUser1 to have read access to dbo.tst1:

So far we just used a GRANT in its most simple form. Now let's see what happens when we use the DENY statement on the same permission:

The DENY statement causes an existing permission to be removed. We have seen that before, REVOKE works the same way.

Let's see if a DENY maybe prevents a re-grant:

It does not. Even after a DENY was executed we can just execute a GRANT to give our principal access to the securable again. That also does work the same way as the REVOKE statement.

But look, the error TestUser1 got after we executed the DENY explicitly states that the SELECT permission was "denied". Maybe there is a hint in that error message. Let's see what error we get after executing a REVOKE:

Darn it - that is exactly the same error message. So it very much looks like REVERT and DENY are just synonyms.

DENY is not the same as REVOKE

If you just look at a single securable and a single principal in isolation, there is indeed no functional difference between the REVOKE and the DENY statements. However, once you start looking at hierarchies of securables or hierarchies of principals, they actually have a very different functionality.

Tomorrow we are going to look at the DENY statement in the context of hierarchies of security principals.

Summary

When looking at a securable and a security principal in isolation there is no discernable difference between REVOKE and DENY. However that is not the full picture, as the next two posts will show. Make sure to come back once they are posted over the next two days.

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.