I’ve never felt the need to allow this, but I saw someone ask the questions recently. Suppose you had a view and you wanted to allow a user to grant permissions to this view to other users.
It’s not a weird edge case, but it’s just not one I’ve normally bothered with. I manage by groups, always, and never want a user to be granting permissions to a specific view. I inherently see users as fragile in the lifetime of an application (administrators as well) and don’t like the idea of allowing them permissions.
If you need to do this, however, there is a way. Let’s examine a basic scenario. I want to allow Bill to grant permissions on the view “MonthlySales”, which I’ve created in AdventureWorks. If I want to allow Bill to use this view, I need to do this:
GRANT SELECT ON MonthlySales TO Bill
If I log into the server as Bill and execute
SELECT * FROM MonthlySales
I get this:
Simple stuff.
However if I want Bill to be able to allow other people in his department to see this view, what can I do?
If I examine the BOL page for GRANT, I see there is an option that can help here. The WITH GRANT option allows the person specified in the statement to grant the existing permissions to others.
Let’s assume I have another user Sue. If I log in as Sue, and I execute the same SELECT that Bill ran above, I get this:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘MONTHLYSALES’, database ‘AdventureWorks’, schema ‘dbo’.
I could easily manage permissions as the DBA, and I prefer this, but for when you have some data that a user can manage, and you are in an environment where things change quickly, perhaps you want to delegate some permissions to users.
Now, let’s change the initial permission I set up for Bill to this:
GRANT SELECT ON MonthlySales TO Bill WITH GRANT OPTION
Bill can still see the view and the data. However Bill can now run this:
GRANT SELECT ON MonthlySales TO Sue
Once he does this, Sue can see the view.
Filed under: Blog Tagged: security, sql server, syndicated 
