REVOKE CASCADE does not work on CONTROL permission

Introduction

Over the last few days we looked in detail at the effect that the CASCADE keyword has on the REVOKE statement, for example in How to REVOKE a GRANT ... WITH GRANT OPTION.

Today I would like to point you to a case in which the CASCADE keyword does not have the expected effect on the permissions hierarchy. In fact, in this special case it does not have any effect at all. The case I am talking about is the CONTROL privilege.

The CONTROL privilege

The CONTROL privilege is the highest privilege that can be granted on any securable in SQL Server. It gives the grantee full "control" over the securable. In particular, it implies any other privilege. You can grant CONTROL on almost any securable. One notable exception is that you can't grant it on a column.

One of the things you can do if you have been granted CONTROL on a securable is granting any permission on that same securable to any other principal.

As we have seen before, if a permission is revoked in SQL Server, the default behavior is to also revoke all permissions that were granted based on that specific permission. This is indicated by the CASCADE keyword, which is in most cases required. Let's see however what happens with the CONTROL permission:

TestUser1 was granted CONTROL on dbo.tst1. Armed with that permission TestUser1 then granted SELECT on the table to TestUser2. Afterwards the CONTROL permission is revoked from TestUser1. However, Testuser2 is still able to access the table.

Now, the first thing to note is that the CASCADE keyword was not specified. It obviously was not required. Maybe the problem is that the child permission was not actually CONTROL but rather SELECT. Let's see what happens if TestUser1 grants CONTROL to TestUser2 instead:

No luck - the child permission did not get removed and the CASCADE keyword seems still to be optional. Maybe actually specifying CASCADE will help? Let's see:

Still no luck. Even though we asked SQL Server explicitly - by specifying the CASCADE keyword - to remove all child permissions when revoking TestUser1's permission, the CONTROL privilege was not removed from TestUser2.

There is one final option we can try. All the examples in my prior posts for which CASCADE was working were looking at permissions that were granted specifying WITH GRANT OPTION. So let's give that a try:

As you can see, even this last option does not work. So we can take away from this execise, that the CASCADE keyword does not work when revoking the CONTROL permission.

Summary

When revoking a grantable permission SQL Server forces us to remove all child permissions as well. However, in the case of the CONTROL privilege, this is not the case. We cannot even ask SQL Server to automatically revoke child permissions. Instead, in this case the CASCADE keyword on the REVOKE statement does not have any effect.