Create procedure permission in schema

  • 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?

  • 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/

  • 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