Post reply

Create and Alter procedure without dbo permissions

  • December 14, 2009 at 2:49 pm

    #207538

    Is it possible to give certain users rights to create and alter procedures (all procedures) in a database, without giving them dbo rights ?

    I've got a user setup with "Create Proc" permissions on a database, but that doesn't seem to do it.

    Any suggestions ?

    Thanks.

  • December 14, 2009 at 2:59 pm

    #1092245

    Check out the db_ddladmin database role in BOL (Books Online) and see if this is what you need.

  • December 14, 2009 at 3:28 pm

    #1092267

    Thank you for your response, but ddl_dbadmin doesn't quite do it.

    If I understand it right, it would allow the person to create and alter tables as well, which is not resired.

    But on the other hand, it would not allow him to grant permissions to the SP's he created.

    We want the person to create & alter procedures as needed, and make the available for production with the appropriate permissions.

  • December 20, 2009 at 8:29 am

    #1094507

    I think that what you want is something like "GRANT ALTER ANY PROCEDURE". Unfortunately that doesn't exist.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 20, 2009 at 8:39 am

    #1094511

    The only way to do this that I can see would be to use "GRANT ALTER TO {user} WITH GRANT OPTION" which gives them the desired permissions, except on everything in the database, then write DDL Triggers for all other objects that would throw and Error and rollback any attempte they made to CREATE or ALTER any other type of object.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 21, 2009 at 4:52 pm

    #1095177

    Thank you for confirming my fears 🙂

    Is it possible to work with schemas in this scenario: allow certain people to create / alter anything they want inside a certain schema ? We could then put the procs and functions inside one (or multiple) schemas and leave views, tables and other stuff outside of it.

    Is that feasible ?

    Thanks again.

  • December 21, 2009 at 5:02 pm

    #1095180

    Yes this would be feasible.

    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 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply