Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

granting execute permissions to ALL stored procedure Expand / Collapse
Author
Message
Posted Tuesday, January 11, 2011 1:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 9:16 AM
Points: 21, Visits: 149
Hi,

I have dozens of stored procedures and need to grant execute permissions to a bunch of users, moreover, I have to do the same for any new user. I google this issue and found few ways to script this: stored procedures, cursors, etc... but I was wondering if there is a more efficient way to do this, lets say group all the stored procedures in one schema (i'm just guessing here) and then grant execute permissions to that schema to my users.

I am using SQL Server 2005.

Thanks.
Post #1046118
Posted Tuesday, January 11, 2011 1:19 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 211, Visits: 844
The way that I have it set up is as you said, to add stored procedures to schemas of which certain roles are granted the relevant permissions.

Users are then added to the roles as necessary.

I do have quite a simplistic set up so there may well be a much cleverer way to achieve what you need.


'Only he who wanders finds new paths'
Post #1046123
Posted Tuesday, January 11, 2011 1:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
i think if you create a role, you can GRANT EXECUTE TO YOURROLE, but that grants execute on all functions as well.
CREATE ROLE [ReallyReadOnly]
GRANT EXECUTE TO [ReallyReadOnly]

AFAIK granting permissions are two levels...you either grant access to everything, or you have to grant each object, one at a time...there;'s not any functionality to grant SELECT/UPDATE/DELETE/EXECUTE to say, just tables, or just views, or just procs...it's access to everything, or the hard way...a loop to do build/execute each statement for each object name.


you can use the metadata like sys.objects to build the GRANT EXECUTE ON [EachObjectName] TO YOURROLE statements, but if you added a new proc, or dropped and recreated the proc, then you have to remember to re-add the permissions again.

I wonder if you could build a DDL trigger that automatically added object names to some roles...


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1046150
Posted Tuesday, January 11, 2011 2:20 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 211, Visits: 844
But could you GRANT EXECUTE ON SCHEMA TO ROLE? That way only the necessary objects are contained and it would simplify future user set ups as they only need adding into the role.

Admittedly there would be some initial pain in adding the objects to the right schema.

I really fancy exploring the DDL trigger method now.....


'Only he who wanders finds new paths'
Post #1046161
Posted Tuesday, January 11, 2011 2:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
david.alcock (1/11/2011)
But you could place certain procs, not all, into a schema and assign the permissions to a role at schema level cant you?

Yes it may be painful to put all the objects in the necessary container to begin with but after then, new users the process is quite simple.



I'm playing with your idea now; can you post an example of how you do it, david?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1046169
Posted Tuesday, January 11, 2011 2:54 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 211, Visits: 844
Im not 100% sure, but would the following work?

GRANT EXECUTE ON SCHEMA::SchemaName TO RoleName

Admittedly I am not sat on SQL right now so cant test and as said, I work in a much simpler environment where I use the GUI for permissions as we dont have that mnay objects in each schema.

EDIT: I dont know how many more get out clauses to put in if it doesnt work!!!!!!!


'Only he who wanders finds new paths'
Post #1046176
Posted Wednesday, January 12, 2011 2:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 211, Visits: 844
Okay, I've just had a look..apologies for the lame naming!

CREATE SCHEMA dba

CREATE ROLE schema_test

GRANT EXECUTE ON SCHEMA::dba TO schema_test

CREATE PROCEDURE dba.Test_Schema
AS SELECT * FROM dbo.MyTestTable

When I view the permissions on the stored procedure it doesnt show anything for the role but on the schema the EXECUTE permission for the role shows correctly, this should be cascaded down through to the objects the schema contains.

I tested earlier on a user added to the role and it appeared to be working.


'Only he who wanders finds new paths'
Post #1046330
Posted Wednesday, January 12, 2011 3:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
david.alcock (1/12/2011)
Okay, I've just had a look..apologies for the lame naming!

CREATE SCHEMA dba

CREATE ROLE schema_test

GRANT EXECUTE ON SCHEMA::dba TO schema_test

CREATE PROCEDURE dba.Test_Schema
AS SELECT * FROM dbo.MyTestTable

When I view the permissions on the stored procedure it doesnt show anything for the role but on the schema the EXECUTE permission for the role shows correctly, this should be cascaded down through to the objects the schema contains.

I tested earlier on a user added to the role and it appeared to be working.


That's how I do it.
If you don't need a fine grain permission set on some procedures, it works.
On the other hand, you could grant execute on the whole schema and then deny execute on the procedures you want to keep away from users.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1046345
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse