Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
This question might do well on dba.stackexchange.com –  Max Vernon Aug 30 '12 at 17:24

1 Answer 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.

share|improve this answer
    
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 '12 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 '12 at 2:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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