January 17, 2010 at 12:06 pm
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?
January 18, 2010 at 1:24 am
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/
January 18, 2010 at 2:15 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply