GRANT … AS: Using GRANT … WITH GRANT OPTION for Roles

Introduction

Yesterday we discussed how to enable users and logins to pass on their permissions. That works great in most cases. However there is a small complication:

Does GRANT ... WITH GRANT OPTION not work on roles?

We know that server roles and database roles can significantly simplify the management of permissions. We also just discovered the ability to delegate permission management. Armed with that information let's try out to combine these two options. First we need to create two logins and two users; we also need a database role to put one of the users in:

Now we can use the GRANT ... WITH GRANT OPTION statement to give all members in TestRole1 the ability to pass on the permission:

That executes without problems:

So we should expect that TestUser1 will now be able to use the GRANT statement to pass the permission on:

However, when we actually try it out, we get a different result:

Unsurprisingly, the permission was also not granted:

Let's review what we did. We know that any permission granted to a role automatically extends to all its members. We also know that GRANT ... WITH GRANT OPTION allows the grantee to pass that permission on. In our case the grantee of the WITH GRANT OPTION is the TestRole1 role, so we expect that the ability to pass on this permission applies to all role members. Finally, we know that TestUser1 is a member of that role. So, we did everything right. What went wrong?

GRANT ... AS

It turns out that the permission to delegation does not automatically transfer from a role to its members like other permission do. However, as a member of a role you can request it from that role, on a case by case basis. The syntax looks like this:

It uses the additional AS clause at the end to specify the role that is supplying the right to use the GRANT statement. If the executing principal is a member of that role, the statement does not fail when executed:

Instead it successfully passes the SELECT permission on to TestUser2 as desired:

Summary

The ability to grant a specific permission does not automatically get passed down to members of a role. Instead it has to be explicitly requested by the role member executing the GRANT. For that the GRANT...AS statement can be used, specifying the role that has the delegation permission.