Create procedure permission in schema

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » Create procedure permission in schema

Create procedure permission in schema

Rate Topic

Display Mode

Topic Options

Author

Message

Vasu-709835

Posted Sunday, January 17, 2010 12:06 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 26, 2012 6:08 AM
Points: 23, Visits: 670

Hi All,

Schema name :net
user : netuser

"netuser" wants to create procedure in "net" schema

How to give create stored proc permission in particular schema to user?

Post #848888

Adi Cohn-120898

Posted Monday, January 18, 2010 1:24 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 2,019, Visits: 4,918

As far as I know, create procedure permissions can be granted (and denied) only on a database level and not on schema level. You could grant it to the user on the database level and create a trigger on CREATE PROCEDURE. In the trigger you can check on which schema the procedure was created and rollback the operation if it isn’t on the correct schema. If you choose to it this way, make sure that you test the trigger, because you’ll might block other logins from creating a procedure.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #849008

SQLRNNR

Posted Monday, January 18, 2010 2:15 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

Exact same question as another user posted. Interesting Coincidence.

Answer:
Grant CREATE PROC to a role. Put Users in that role.
Grant ALTER SCHEMA on the schema(s) that the Users need to modify stored procedures in to the role.
Grant VIEW DEFINITION on the schema(s) that the Users need to modify stored procedures in to the role.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #849400

« Prev Topic | Next Topic »

Permissions