SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Granting Permission to Grant Permissions

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

Bill can still see the view and the data. However Bill can now run this:


Once he does this, Sue can see the view.

Filed under: Blog Tagged: security, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...