Post reply

Stored Procedure permission

  • April 14, 2009 at 3:21 pm

    #132818

    Hi all,

    A user complained that he is not able to modify a stored procedure in the database. I checked the properties for the user, Under database rold membership all the roles are checked. In the securables, I see the stored procedure list but it only has execute permission checked. Is it the alter permission to allow the user to modify the stored procedure??

    Thanks again.

  • April 14, 2009 at 4:46 pm

    #977751

    Yes. Something to keep in mind about db_ddladmin is that, while it allows members to create objects, the objects are owned by whomever created them not by DBO. So, if your developer didn't created the stored procedure, he wouldn't be able to modify it by default.

    Greg

  • April 14, 2009 at 5:49 pm

    #977772

    Thanks Greg

  • April 15, 2009 at 6:51 am

    #978019

    Greg Charles (4/14/2009)

    Yes. Something to keep in mind about db_ddladmin is that, while it allows members to create objects, the objects are owned by whomever created them not by DBO. So, if your developer didn't created the stored procedure, he wouldn't be able to modify it by default.

    Greg

    Greg,

    In 2005+ wouldn't the object be put in the user's default schema? I have not tested this, but since schema-user separation was introduced I would have thought this would be the case.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • April 15, 2009 at 8:07 am

    #978092

    I’ve granted the user alter, control and execute permission to the stored procedure. But still the user is not able to modify the proc. And the user properties, under the database role membership all the roles are selected like db_accessadmin,…, db_securityadmin. What else could be the issue?? Could it be the windows authentication for the user?

    Thanks a lot

  • April 15, 2009 at 8:14 am

    #978101

    You can explicitly grant the permission to the user to modify the stored proc by usering "GRANT" statement.

    Abhijit - http://abhijitmore.wordpress.com

  • April 15, 2009 at 8:23 am

    #978108

    Thanks. I actually granted explicit alter permission on the stored procedure via SSMS. Is this different from running a grant command?

    From the software, when the user runs a program, it drops and re-creates the stored procedure. Will it be enough to grant just the alter permission?? I thought the user will need control too, since the program drops the proc.

    Also, Is there a way to find out if the user has DML permissions for the tables in the stored proc?

    Thanks again for all your help

  • April 15, 2009 at 9:07 am

    #978153

    Jack Corbett (4/15/2009)

    Greg Charles (4/14/2009)

    Yes. Something to keep in mind about db_ddladmin is that, while it allows members to create objects, the objects are owned by whomever created them not by DBO. So, if your developer didn't created the stored procedure, he wouldn't be able to modify it by default.

    Greg

    Greg,

    In 2005+ wouldn't the object be put in the user's default schema? I have not tested this, but since schema-user separation was introduced I would have thought this would be the case.

    You're right Jack. I guess I was thinking about SQL 7/2000.

    Greg

  • April 15, 2009 at 9:09 am

    #978155

    Currently the user has explicit permission to alter,control and execute on the stored procedure. But still the user is not able to create the procedure from the software. I assume with the control permission the user should be able to drop and re-create the procedure right?? Will it depend on what permissions the user has at the windows level because we use windows authentication for the login?

    Thanks a lot

  • April 15, 2009 at 9:27 am

    #978174

    So, the procedure is created by an application? Does the application connect to SQL Server using the user's login or with it's own application login?

    Greg

  • April 15, 2009 at 9:30 am

    #978177

    Yes, the application re-creates the procedure.. and it connects as the user's login there is no seperate application login..

    Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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