How to REVOKE just the GRANT OPTION

Introduction

Yesterday we talked about what you have to do to revoke a permission that was granted specifying the WITH GRANT OPTION. Today I would like to show you how to remove just the grant option without revoking the permission itself.

REVOKE GRANT OPTION Example

First we need to GRANT a grantable permission to TestUser1 and then have TestUser1 grant that permission to TestUser2. We going to use the same T-SQL batch for this task that you have seen before, for example in yesterday's post

Now with all those permissions in place we can try to revoke just the grant option from TestUser1. For that we need to add the specification GRANT OPTION FOR to the REVOKE statement, right in front of the privilege, like this:

As before, because we are removing the ability for TestUser1 to grant the permission, we also have to specify the CASCADE keyword. This will cause all permissions that were granted by TestUser1 using this specific permission to be revoked too. Let's see this in action:

The ability to grant was removed successfully. However, the SELECT privilege itself was preserved. TestUser1 is still able to select from the table. However, TestUser2 is now not so lucky anymore. The SELECT permission was successfully and completely removed from TestUser2:

Summary

The REVOKE GRANT OPTION statement can be used to remove just the grant option from a granted permission without revoking the permission itself. Because the ability to grant is removed, SQL Server requires the use of the CASCADE keyword, which causes all permissions that were granted based on this permission to be revoked recursively.