locked
Give access to create alter and execute SP's in SQL server 2008R2 RRS feed

  • Question

  • Hello,

    Is there a way i can grant a windows authenticated user say abc\adam privilege:

    -> to create, execute and alter the stored procedures that will be created by him only:

    Point is we do not want the user to execute alter or modify other stored procedures out there for that DB

    Please advise!

    Tuesday, May 12, 2015 3:52 PM

Answers

  • Adam does need ALTER permission on the schema, but should get it because Adam owns the schema, not because he's granted ALTER.  Granting ALTER on the schema is unnecessary and dangerous, because if you don't change the schema ownership, Adam will have intact ownership chains from schema objects he creates to other objects owned by the schema owner.

    Either when creating the schema

    create schema adam authorization adam

    or after it's created

    alter authorization on schema::adam to adam

    David


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


    Wednesday, May 13, 2015 4:41 PM
  • Drop the user and the schemas and start over. 

    create user [abc\adam] for login [abc\adam] with default_schema = adam
    create schema adam authorization [abc\adam]
    David


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

    • Marked as answer by MAXKA Thursday, May 14, 2015 2:03 PM
    Wednesday, May 13, 2015 7:36 PM
  • You don't need to drop teh user and start from scratch.

    Instead you can either change the ownership of the schema named [adam]:

    ALTER AUTHORIZATION ON SCHEMA::[adam] TO [abc\adam]

    ...or change the user default schema to the one you want:

    ALTER USER [abc\adam] with default_schema = [abc\adam]

     I hope this helps,

    -Raul Garcia

      SQL Server Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by MAXKA Thursday, May 14, 2015 2:02 PM
    Wednesday, May 13, 2015 7:47 PM

All replies

  • Well.  Adam needs a user, that user needs to own a schema, and needs the right to create procedures, and the rights to do the things that the stored procedure does.

    eg

    create user adam for login [abc\adam] with default_schema = adam

    grant create procedure to adam grant select on schema::dbo to adam

    It's imperative that adam only has the rights to create procedures in his own schema, and not in a schema owned by any other user.  Otherwise ownership chaining would allow adam to escalate privileges.

    David


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


    Tuesday, May 12, 2015 4:05 PM
  • Thanks David,

    I am not sure what was done wrong , but i get below message when login abc\adam with user admin tried to create a SP:

    The specified schema name "dbo" either does not exist or you do not have permission to use it.


    • Edited by MAXKA Tuesday, May 12, 2015 4:41 PM
    Tuesday, May 12, 2015 4:32 PM
  • Very likely either the default schema for that user is “dbo”, or he/she is explicitly creating a module on “dbo” schema.

    David’s suggestion works in such way that each user will have his/her own schema to control and create objects on it, but they cannot alter objects on somebody else’s schemas (i.e. abc\adam owns and can alter schema [abc\adam], but he does not have ALTER permission on schema [dbo]).

    Try explicitly creating the object in his own schema. i.e.

    CREATE PROC [abc\adam].[sp_my_sp]
    AS …
    I hope this information helps,

    -Raul Garcia

    SQL Server Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, May 12, 2015 6:35 PM
  • Also correcting grant select on schema::dbo to adam

    Adam needs ALTER permission on the schema that will contain the procedures.

    GRANT ALTER ON SCHEMA::<schema_name> TO [abc\adam];


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

    Wednesday, May 13, 2015 4:08 PM
  • Adam does need ALTER permission on the schema, but should get it because Adam owns the schema, not because he's granted ALTER.  Granting ALTER on the schema is unnecessary and dangerous, because if you don't change the schema ownership, Adam will have intact ownership chains from schema objects he creates to other objects owned by the schema owner.

    Either when creating the schema

    create schema adam authorization adam

    or after it's created

    alter authorization on schema::adam to adam

    David


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


    Wednesday, May 13, 2015 4:41 PM
  • Thanks @Raul,

    After creating and authorizing schema as mentioned by David below:

    User can simply create SP , also as you mentioned 

    CREATE PROC [abc\adam].[sp_my_sp]

    But, when created without being explicit,

    I can see SP by default taking the name like adam.testSP. Any Idea how come?

    Wednesday, May 13, 2015 6:40 PM
  • @Rick, 

    When i try to Grant alter on schema as said i am getting below error message:

    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    Wednesday, May 13, 2015 6:42 PM
  • >But, when created without being explicit,

    >I can see SP by default taking the name like adam.testSP. Any Idea how come?

    Looks like you have two different schemas [adam] and [abc\adam], and [adam] is the user's default schema.

    David


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

    Wednesday, May 13, 2015 6:50 PM
  • David@, i checked and can see only one schema [adam] :(
    Wednesday, May 13, 2015 7:10 PM
  • Drop the user and the schemas and start over. 

    create user [abc\adam] for login [abc\adam] with default_schema = adam
    create schema adam authorization [abc\adam]
    David


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

    • Marked as answer by MAXKA Thursday, May 14, 2015 2:03 PM
    Wednesday, May 13, 2015 7:36 PM
  • You don't need to drop teh user and start from scratch.

    Instead you can either change the ownership of the schema named [adam]:

    ALTER AUTHORIZATION ON SCHEMA::[adam] TO [abc\adam]

    ...or change the user default schema to the one you want:

    ALTER USER [abc\adam] with default_schema = [abc\adam]

     I hope this helps,

    -Raul Garcia

      SQL Server Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by MAXKA Thursday, May 14, 2015 2:02 PM
    Wednesday, May 13, 2015 7:47 PM