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


granting execute permissions to ALL stored procedure


granting execute permissions to ALL stored procedure

Author
Message
R Oliveira
R Oliveira
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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.
david.alcock
david.alcock
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 1195
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28090 Visits: 39930
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
david.alcock
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 1195
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28090 Visits: 39930
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
david.alcock
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 1195
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
david.alcock
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 1195
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'
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9836 Visits: 13350
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search