Allowing a user to create DBO procedures.

Question

Allowing a user to create DBO procedures.

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
September 17, 2007 at 12:23 pm

#95397

We granted CREATE PROCEDURE rights to a role. Then assigned a user to the role. Now the user can create procedures under their own id but can not create or modify DBO procedures. Is there any way to do this in SQL 2000?
Thanks
Kenneth Fisher
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

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 1

This probably doesn't help, but the right way to do it would be to deny all users the ability to create procs and to follow a code promotion process.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

I assume you want a user to be able to create stored procedures but not any other objects such as defaults, functions, rules, tables, or views.

Jeff's approach is prefered as you only need to grant create procedure and do a code review before creating the procedure as owned by dbo,

This approach is very bizare but works because deny overrides any grants or roles. The deny does NOT override privileges for the database role db_owner or for the server role sysadmin.

1. Create a role such as "db_sponly"

2. To the role "db_sponly", deny create for all the object types except stored procedures.

deny create DEFAULT, create FUNCTION, create RULE, create TABLE, create VIEW to db_sponly

3. For each user you want to be able to create stored procedures, grant the role ddl_admin AND the role "db_sponly".

4. Amazingly, the user can now create a procedure but they cannot execute the procedure they just created! Try granting db_securityadmin and remind the user that they must grant themselves execute rights on the procedure.

You will also need to grant insert, update, delete and references privileges to tables and views as appropriate.

If all tables and views in the database, roles db_datareader and db_datawriter can be granted.

P.S. I implemented this is 2002 and just retested, so it does work. But expect to get phone calls about "i cannot execute a procedure".

SQL = Scarcely Qualifies as a Language

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

Worked great

We ended up adding the DDL_Admin to the other role so we didn't have to add it to each user. My understanding is that in 2005 we will be able to add execute rights on the dbo schema and get rid of the execute problem as well.

Thanks

Kenneth Fisher

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]