How to REVOKE a GRANT … WITH GRANT OPTION

Introduction

A few days ago we investigated the special WITH GRANT OPTION syntax of the GRANT statement that can be used to delegate permission management. Today I want to look at what happens when you are trying to REVOKE a permission that was granted using WITH GRANT OPTION.

WITH GRANT OPTION Example

Just as a quick refresher, if you grant a permission specifying WITH GRANT OPTION to a principal, like this:

That principal can turn around and grant that same permission to someone else:

As you can see, TestUser1 was able to GRANT the SELECT permission to TestUser2. Now let's try to REVOKE that SELECT privilege from TestUser1 again.

Revoking a Grantable Permission

When asked the question of how to revoke a grantable permission, meaning a permission that was granted specifying WITH GRANT OPTION, the solution that comes to mid first is to just execute a straight forward REVOKE. However, I wouldn't write a post about this, if it were that simple. Let's try it out:

The error says that we have to specify the CASCADE option when revoking a grantable permission.

A quick check in the manual reveals that the syntax of the CASCADE option is very unassuming:

The CASCADE key word is just appended to the end of the REVOKE statement. The effect this addition has however is everything but unassuming. It specifies that all permissions granted based on the permission to be removed should be removed too. And, as you can see in the error message above, it is not optional. To understand what this means let's look at an example.

REVOKE with CASCADE

In the first example in this post we had granted TestUser1 a grantable SELECT privilege. TestUser1 then granted the same privilege to TestUser2. Now let's execute a cascading REVOKE and watch TestUser2 closely:

As you can see, even though the REVOKE statement specified to revoke from TestUser1, the permission that was granted to TestUser2 was removed too.

Keep that in mind when planning to delegate your permission management. If the person you are delegating to ever falls in disgrace, you cannot revoke the grantable permission without undoing all the work that was done based on it.

Summary

When revoking a permission that was granted specifying WITH GRANT OPTION, SQL Server forces us to specify the CASCADE keyword. That causes all permissions that had been granted based on the one we are revoking to be revoked too.