Post reply

SQL 2005 security

  • March 28, 2006 at 2:16 pm

    #152864

    Hi everyone,

    I just tried to post and for some reason it seemed like it failed, so forgive me if this turns out to be a duplicate.

    My question is this. In our SQL 2K environment right now we are having an issue with users who create Stored Procedures in the dbo schema and are then not able to execute the SPs. They call me and I give them execute permissions. This is a problem if they are working late or otherwise have a hard time reaching me. Is there any way in SQL 2005 that a user can be given the permission to create SPs in the dbo schema and then be able to also execute it? Thank in advance!

  • March 28, 2006 at 3:34 pm

    #629312

    For your SQL 2K environment, you should have the users assigned to a database role that has execute permissions on all stored procedures.

    Then when users create procedures they can include a GRANT statement to assign the permissions. If they don't have rights to execute the GRANT statement then you can setup a stored procedure to do it. Then you can setup the stored procedure to run from a scheduled job.

    Sorry, not sufficiently up to speed on SQL 2K5 security to be able to give an answer on that part.

     

    --------------------
    Colt 45 - the original point and click interface

  • March 29, 2006 at 3:18 am

    #629415

    From the original post it apeears that the users create and execute the procedures.

    If you set a default schema for the users, then the sps will also belong to that schema and inherit the execute permissions already granted.

  • March 29, 2006 at 9:12 am

    #629526

    Thank you for your replies.

    I am not sure what you mean by setting a default schema. How is that done?

    We are probably going to do a variation of what Phill suggested. But what is in store for us in SQL 2005? Anyone know? I see in the SQL 2005 BOL that there is a "grant permission on schema" command and execute is listed as one of the permissions that can be granted. Does that mean that I can say that user X can have execute permissions to any SPs in schema Y?

    Thanks again!

  • March 29, 2006 at 11:10 pm

    #629715

    HI,

    User should have permission on the tables used in a stored procedure to excute the procedure.

    The user can create the procedure and even grant the stored procedure to other users.

    but those user should have permission on the tables used in stored procedure.

    From

    Killer

  • March 30, 2006 at 1:50 am

    #629738

    My response is for SQL 2005.

Viewing 6 posts - 1 through 5 (of 5 total)

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