granting execute permissions to ALL stored procedure

Question

granting execute permissions to ALL stored procedure

R Oliveira

SSC-Addicted

Points: 499
More actions
January 11, 2011 at 1:14 pm

#243469

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

david.alcock SSCertifiable Points: 6021 More actions · Answer 1

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'

Lowell SSC Guru Points: 323504 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

david.alcock SSCertifiable Points: 6021 More actions · Answer 3

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'

Lowell SSC Guru Points: 323504 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

david.alcock SSCertifiable Points: 6021 More actions · Answer 5

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'

david.alcock SSCertifiable Points: 6021 More actions · Answer 6

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'

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

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