Post reply

Database Roles

  • May 8, 2007 at 9:05 am

    #174024

    Hi All,

    I have a question regarding the pre defined db roles in 2000 with relation to executing functions.

    My login is a member of the following roles;

    db_ddladmin, db_datawriter and db_reader.

    When I execute a sp that I have created on the database it fails as execute permission is denied on a function within the database under the dbo schema. What is the reason for this? Is there not a role that will give me execute permission on all functions within the db?

    Thanks guys.

  • May 8, 2007 at 9:12 am

    #704958

    Add a GRANT EXECUTE permission at the end of the SP to either a user list or group. See the command syntax for details.

    Or, go to the SP list right click, properties and do it there.

  • May 8, 2007 at 9:20 am

    #704965

    Hi Camilo,

    I cannot right click and use properties as I do not have the relevant access on the database to amend the properties.

    Also I wasn't very clear in my first post. The stored proc I created runs but throws up an error when it gets to the point where it calls a function within the dbo schema on the same database.

    I want to inform the dba of what roles I require within the database so that I can run this proc successfully. I was hoping one of the pre-defined roles would grant me execute permission on every function within the database.

    If now I am going to have to explictly ask for execute permission to be granted on each specific function within the database I need access to.

    Does this make sense?

    Thanks.

  • May 8, 2007 at 5:05 pm

    #705105

    None of the fixed database roles you mention grant EXECUTE permissions.

    A user who is only going to execute stored procedures and the user defined functions in them only needs to be granted EXECUTE permission for the procedures and functions.  No other permissions or fixed database role is needed.

    Greg

    Greg

  • May 9, 2007 at 7:57 am

    #705219

    To save maintenance you can create a role to put your users in for and grant EXECUTE on individual procs and/or functions to the role instead.

  • May 9, 2007 at 9:57 am

    #705280

    Thanks all for you kind replies.

    I will have to pass these on to our DBA!

  • May 9, 2007 at 10:08 am

    #705285

    This really isn't helpful to Adam's question, but I can't believe no one commented on the fact that a database user/developer would be in the position of researching and recommending what roles he should have to the DBA.

    Mattie

     

  • May 9, 2007 at 10:53 am

    #705307

    add your users to the db_owner role...

  • May 10, 2007 at 8:52 am

    #705524

    "add your users to the db_owner role..."

    Come on.  The guy just wants to create and execute stored procedures.  He doesn't need all the permissions that the db_owner role grants.

    Greg 

    Greg

Viewing 9 posts - 1 through 8 (of 8 total)

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