1

I would like to grant someone on our web dev team the ability to grant other users (other web developers as well as application users they create) datareader, datawriter, and exec sproc in our Dev and Test environments.

What do I need to give him to be able to accomplish this? I want to give him the minimum to accomplish this.

1

1 Answer 1

1

Quoth the scripture:

Adding members to flexible database roles requires one of the following:

  • Membership in the db_securityadmin or db_owner fixed database role.

  • Membership in the role that owns the role.

  • ALTER ANY ROLE permission or ALTER permission on the role.

Adding members to fixed database roles requires membership in the db_owner fixed database role.

That said, I would

  • create role [Developers] authorization [dbo]
  • grant select, insert, update, delete, execute to [Developers]
  • grant alter on role::[Developers] to [some developer]

Then, by bullet point three from books online, user [some developer] can add anyone to the Developer role and they'll get the permissions that the role grants.

Consider getting more granular in your granting though. For instance, if your user objects are in a schema, consider granting permissions at a schema level rather than on everything in the database as I've done above by not mentioning a securable in the GRANT statement.

2
  • That is the right direction, but what I want the role members to be able to grant exec on specific stored procedures to specific users. For example, when they are working on a new app, they create a user that that app impersonates. They then want to grant exec to the app user only on the sprocs and tables that the app needs. Does this make sense?
    – Sean
    Aug 29, 2012 at 18:07
  • That totally makes sense. Would different users need access do different objects? If not, the way I typically handle the scenario you're talking about is to create a role called "App" and grant whatever permissions to it and put the application user(s) in that role. One place to manage the permissions and you can deploy that across environments w/o fear of some users not being in all environments.
    – Ben Thul
    Aug 30, 2012 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.