Create a role for editing stored procedures

Question

Create a role for editing stored procedures

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
July 6, 2012 at 10:16 am

#271997

I'm trying to create a role to give to developers to edit stored procedures. I've granted it CREATE PROCEDURE at the database level but the user is still not able to alter existing stored procedures, or create new ones unless either he or the role is the owner of the schema.
I'm sure I'm missing something simple but for the life of me I can't think of what it is.
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Kenneth.Fisher (7/6/2012)
I'm trying to create a role to give to developers to edit stored procedures. I've granted it CREATE PROCEDURE at the database level but the user is still not able to alter existing stored procedures, or create new ones unless either he or the role is the owner of the schema.
I'm sure I'm missing something simple but for the life of me I can't think of what it is.
Thanks
Kenneth

Can you define the specific requirements? is it Alter Only any old stored procedure, or only sptred procedures that currently exist PLUS what they create?

is it all objects(ie create table,function, views as well?

SQL doesn't have the granularity at the object type level;

it's pretty much create something for specific objects, or all objects.

you could add a DDL trigger to prevent other types of object changes after you granted ALTER though, as a work around.

this grants alter to tables,views,functions and procs int he dbl schema, for example

Grant ALTER ON SCHEMA::[dbo] To [MyDeveloperRole]

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!

Lowell SSC Guru Points: 323518 More actions · Answer 2

to get just the existing procs, you want to use the sys tables to help generate the commands you need:

SELECT

'GRANT ALTER ON ' + schema_name(schema_id) + '.' + quotename(name) + ' TO [MyDeveloperRole];',*

FROM sys.procedures

WHERE type_desc='SQL_STORED_PROCEDURE'

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!

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

I want him to be able to alter/create any SP in the database, but ONLY sps. No functions, views. No modifying tables etc. I've done this in the past by putting the user in ddl_admin then specifically denying create function, view etc. I'm just trying to find a better cleaner solution.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]