DENY CASCADE does not CASCADE the DENY

Introduction

One of the more rarely used forms of the DENY statement is DENY CASCADE. I have to admit that you would need to use it only in kind of an edge case. However, if you ever come across it, it does not actually do what it says is does.

DENY CASCADE Example

To see what it is actually doing let's look at an example. First we need two users, a schema and a table:

The DENY CASCADE statement looks similar to the REVOKE CASCADE statement that I described two weeks ago in How to REVOKE a GRANT … WITH GRANT OPTION. It seems to have to do with permissions that where granted based on a grantable grant. So to start out we are going to GRANT SELECT WITH GRANT OPTION on our table to TestUser1 and have TestUser1 then GRANT SELECT to TestUser2:

We know already that we cannot just call REVOKE on the grantable permission but instead have to use REVOKE CASCADE. Let's try if we can simply DENY the permission:

SQL Server does throw the same error message for both REVOKE and DENY when used without the CASCADE keyword against a grantable permission. So let's try to use it with that keyword:

It actually did invalidate the permission of TestUser2 too. So the CASCADE keyword does cause some kind of cascading. But what does it actually do with those child level permissions? If you just go by the sound of the statement itself, you are probably thinking that it denies the permission to TestUser1 and all users that TestUser1 has granted it to. However, that is not the case.

DENY CASCADE merely executes a REVOKE CASCADE followed by a simple DENY. The DENY itself does not get cascaded.

To confirm that, we need to use an indirect testing technique. In my part 3 of my series comparing the DENY and REVOKE statements we talked about Hierarchies of securables. There we saw that a GRANT on say a schema gets overridden by a DENY on the table itself.

We can re-confirm that quickly by granting SELECT on TestSchema1 to TestUser1:

As expected, that GRANT did not cause TestUser1 to be able to access the table. Now, if the SELECT privilege was actually denied to TestUser2 as well, we should see the same behavior when granting SELECT on the schema to TestUser2. Let's try:

As you can see, a simple GRANT SELECT on TestSchema1 to TestUser2 gives TestUser2 access to the table again. That means, what the DENY CASCADE did to the SELECT privilege of TestUser2 cannot have been a DENY, only a REVOKE.

Summary

While DENY CASCADE might sound like the DENY is being cascaded, that is not actually the case. Instead it just acts like a REVOKE CASCADE followed by a simple DENY without the CASCADE option.