none
Permission - alter, view and execute all the stored procedures RRS feed

  • Question

  • Is there a way to grant a user to alter, view and execute all the stored procedures in a database?

    I have set him as a db_writer, but I don't think that includes above access?

    Thanks

    SQLFriend

    Wednesday, December 19, 2012 8:22 PM

Answers

All replies

  • You can have more than one schema in a database.  The following snippet shows you how to grant exec permission to a user for a schema:

    use [AdventureWorks2012]
    GO
    GRANT EXECUTE ON SCHEMA::[HumanResources] TO [TestUser]
    GO 


    Tom
    SQL Server MVP
    Toronto, ON Canada


    • Edited by Tom Moreau Wednesday, December 19, 2012 9:02 PM
    • Proposed as answer by Tom Moreau Wednesday, December 19, 2012 9:03 PM
    • Unproposed as answer by msloy Wednesday, December 19, 2012 9:39 PM
    Wednesday, December 19, 2012 9:01 PM
  • This is to grant execute on a schema, how about view and edit all stored procedures.

    Thanks


    SQLFriend

    Wednesday, December 19, 2012 9:41 PM
  • This is to grant execute on a schema, how about view and edit all stored procedures.

    Thanks


    SQLFriend

    Here ya go:

    GRANT ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::[HumanResources] TO [TestUser]
    


    Tom
    SQL Server MVP
    Toronto, ON Canada

    • Proposed as answer by Tom Moreau Wednesday, December 19, 2012 9:47 PM
    Wednesday, December 19, 2012 9:47 PM
  • Does this mean you can have alter, execute, view definition on all the objects in a schema?

    That includes tables, views, functions, stored procedures etc.

    We have quite a few schemas in the database, we will have to grant to each schema, other than doing this or giving dbowner permission, any other recommendations?

    Thanks


    SQLFriend

    Wednesday, December 19, 2012 10:00 PM
  • You can also grant these permissions on database level:

    GRANT ALTER, EXECUTE, VIEW DEFINITION TO [TestUser]

    However, this also gives ALTER on all tables, which may or may not be desireable.

    If you want to grant permission to change any stored procedures, but no tables, you will need to put them in different schemas and grant permissions per schema.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 19, 2012 10:37 PM
  • Thanks, this is more like waht I want.

    SQLFriend

    • Marked as answer by msloy Thursday, December 20, 2012 5:03 PM
    Thursday, December 20, 2012 5:03 PM