GRANT CREATE/ALTER PROCEDURE

  • I believe I can run this statement so that TestUser can create procedures in the Database.

    GRANT CREATE PROCEDURE TO TestUser

    I also believe there is NO "GRANT ALTER PROCEDURE TO TestUser" statement. So I have to "GRANT ALTER to Schema". Which allows ALTER to all objects, which I do not want.

    How can I, or maybe I can not, enable the developer to ALTER the PROCEDURE and DROP the PROCEDURE after I GRANT them the CREATE PROCEDURE permission? Without allowing them to perform other DDL commands?

    ...thanks

  • Have you tested altering a proc after granting create? IIRC, create also grants the ability to alter.

    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

  • Thanks for the reply.

    I GRANT CREATE PROCEDURE to the User, logged into SSMS as that user and tried to create an SP.

    It FAILED.

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

    DBO does exist.

    I did notice that there is a LOCK icon over all the existing SPs?!

    I then tried a sp_helptext of an existing SP and it FAILED.

    "There is no text for object 'dbo.MyProcedure'.

    I'm missing some other type of permission to even VIEW existing SPs.

    Based on BOL, the User must also have "ALTER PERMISSION ON THE SCHEMA"?!

    Is this correct? Doesn't this mean that can ALTER ANY Object in the Database? I do not want this.

    ...thanks

  • I did a "GRANT VIEW DEFINITION to TestUser", so now the LOCK ICONs are gone and I can do a sp_helptext.

    However, TestUser still can NOT Create an SP. I'm missing some other permission other than CREATE PROCEDURE permission.

  • Looking at BOL, it says that besides CREATE PROCEDURE, the User must also be GRANTed ALTER Schema like this?

    GRANT ALTER ON SCHEMA::dbo TO TestUser

    Doesn't that give the User too much authority to ALTER anything in the Schema?

    I just need to enable the User to Create a Stored Procedure?

  • Are you trying to create a proc as follows:

    Create procedure dbo.someproc

    or

    Create procedure testuser.someproc

    ?

    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

  • Yes, as DBO.

    I ended up doing a GRANT ALTER as stated by BOL. That worked. Although I think it allows too many permissions.

  • Yes that will give them more permissions than you want on dbo.

    If your goal is to allow them to do that under the dbo schema, then that is what you will need to do. If your goal is to simply allow them to create and modify their own procs, then they would need to create a proc with their schema name in lieu of dbo.

    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

  • CirquedeSQLeil (9/25/2010)


    Yes that will give them more permissions than you want on dbo.

    If your goal is to allow them to do that under the dbo schema, then that is what you will need to do. If your goal is to simply allow them to create and modify their own procs, then they would need to create a proc with their schema name in lieu of dbo.

    Yep, the SPs must be under DBO. So ALTER is the solution.

    ...thanks

  • You're welcome

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

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