Home » Blog » WITH GRANT OPTION: How to Delegate your Permission Management

WITH GRANT OPTION: How to Delegate your Permission Management

2014-01-25 - General, Security

Introduction

In yesterday's post about the T-SQL GRANT statement we discussed how to manage permissions in T-SQL. Usually, if you grant a specific permission to a security principal in SQL Server, that principal will not be able to turn around and grant that same permission to someone else. That is normally a good thing, as it makes sure that permission management stays under your control. But sometimes it is nice to delegate…

WITH GRANT OPTION

If you have enough trust in a security principal (or the human behind that principal) that you want to allow them to pass on the permission as they see fit, you can use the WITH GRANT OPTION extension to the GRANT statement. The syntax looks like this:

[sql] GRANT SELECT ON OBJECT::dbo.tst TO TestUser1 WITH GRANT OPTION;
[/sql]

This is almost the same statement that we saw yesterday. The only difference is that three words were added to the end: WITH GRANT OPTION. Those three words enable permission delegation. Let's look at an example.

WITH GRANT OPTION in Action

First let us confirm that a simple GRANT without WITH GRANT OPTION does not allow for delegation:

[sql] GRANT SELECT ON OBJECT::dbo.tst TO TestUser1;
GO
EXECUTE AS USER='TestUser1';
GO
SELECT * FROM dbo.tst AS T;
GO
GRANT SELECT ON OBJECT::dbo.tst TO TestUser2;
GO
REVERT;
[/sql]

Executing these statements leads to this result:

As you can see, the permission to SELECT was granted to TestUser1. However, the attempt by TestUser1 to delegate that permission to TestUser2 fails with an - a little misleading - dbo.tst not found error.

If TestUser2 now tries to access the data in that table the statement fails:

Now let's look at how the WITH GRANT OPTION changes this picture by running this statement block, starting out with the extended GRANT:

[sql] GRANT SELECT ON OBJECT::dbo.tst TO TestUser1 WITH GRANT OPTION;
GO
EXECUTE AS USER='TestUser1';
GO
SELECT * FROM dbo.tst AS T;
GO
GRANT SELECT ON OBJECT::dbo.tst TO TestUser2;
GO
REVERT;
[/sql]

First thing to notice is that this does not throw an error anymore:

That should mean that TestUser2 can now access the table. Let's confirm:

The delegation of the SELECT permission by TestUser1 to TestUser2 was successful.

Summary

Sometimes it is required for a security principal to be able to delegate a specific permission to other security principals. The simple GRANT statement does not allow for that. However, to enable delegation, the GRANT statement extension WITH GRANT OPTION can be used.