locked
Limit CREATE PROCEDURE to owned schema RRS feed

  • Question

  • Is it possible to allow a SQL user to create stored procedures in a schema they own, but prevent them from creating them in any other schema (like dbo)?  Seems like it is possible to do this with EXECUTE (which is not even required if they own it) but not CREATE:

    GRANT CREATE PROCEDURE TO Developer 
    GRANT EXECUTE ON SCHEMA::OwnedSchema TO Developer 

    If "ON SCHEMA" is included in the grant for create, the following syntax error is returned:

    Msg 102, Level 15, State 1, Line 0
    Incorrect syntax near 'CREATE PROCEDURE..'.

    Thursday, January 30, 2014 4:12 PM

Answers

  • CREATE PROCEDURE is database-wide, but does not enable a user to actually create a procedure in any schema.  For that they would additionally require ALTER on a schema.  So your desired behavior is exactly what you get from granting CREATE PROCEDURE.  They will be able to create procedures only in schemas they own.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Doug Dekker Thursday, January 30, 2014 6:50 PM
    Thursday, January 30, 2014 5:19 PM

All replies

  • CREATE PROCEDURE is database-wide, but does not enable a user to actually create a procedure in any schema.  For that they would additionally require ALTER on a schema.  So your desired behavior is exactly what you get from granting CREATE PROCEDURE.  They will be able to create procedures only in schemas they own.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Doug Dekker Thursday, January 30, 2014 6:50 PM
    Thursday, January 30, 2014 5:19 PM
  • To expand upon David's answer. The correct steps are:

    GRANT CREATE PROCEDURE TO Developer;

    GRANT ALTER ON SCHEMA::OwnedSchema TO Developer;


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, January 30, 2014 5:35 PM
  • Confirmed the following statement returns a security exception:

    CREATE PROCEDURE [dbo].[TestProc] (@echoNumber as int) AS
    SELECT @echoNumber
    GO

    Output: 
    Msg 2760, Level 16, State 1, Procedure TestProc, Line 2
    The specified schema name "dbo" either does not exist or you do not have permission to use it.

    Thursday, January 30, 2014 6:45 PM