execute procs,data read and data write permission for users

  • 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

  • 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
  • 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.

  • 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.

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

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