Post reply

execute procs,data read and data write permission for users

  • January 31, 2011 at 6:12 am

    #233584

    Hi All

    I want the user to be able execute procs,data read and data write permission for a database. Vendor is suggesting me to give DBO permission but I am not happy with it as users can drop the database as well.

    Is there a way I can only give execute procs,data read and data write permission for users?

    Thanks

  • January 31, 2011 at 6:24 am

    #1280374

    db_datareader, db_datawriter roles and then grant execute on the schema that the procedures are in. If it's the dbo schema, then...

    GRANT EXECUTE ON SCHEMA::dbo TO <username>

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 3, 2011 at 11:06 am

    #1282047

    Yeah I take this direction and added a db_role called Exec_SP or db_Exec giving the role the securable and just adding users to the role. To me it seems more transparent.

  • February 4, 2011 at 7:48 am

    #1282477

    Call me crazy but I don't like giving db_datareader and db_datawriter permissions. I prefer to create my own custom roles that have only the needed permissions and then assign users to that role. You could do this by assigning rights at the schema level or at the object level. The nice thing, but also the not so nice thing, about schema level permissions is that they apply to any objects created in the schema at any time.

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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