Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm establishing a shared development instance for multiple single-database applications.

I'm envisioning granting authorized users permissions to allow any schema and data modifications they need to perform, but want to prevent the same users from issuing ALTER DATABASE commands to ADD or MODIFY FILEs. This is to prevent any one developer from consuming additional disk space resources without consent from the DBA.

Looking at an approach using the least administrative effort, I'm thinking I should grant db_owner to the authorized users and add a database or server trigger to block ALTER DATABASE commands unless they come from DBA sessions.

Would this work or is there a better/safer approach?

share|improve this question
    
Why not grant them permissions at the schema level instead of giving them db_owner? –  Shawn Melton Oct 10 '12 at 16:32
    
See this dba.stackexchange.com/a/6921/630 please –  gbn Oct 11 '12 at 7:00
add comment

1 Answer 1

up vote 0 down vote accepted

You can put the users in the db_ddladmin, db_datawriter, and db_datareader roles. This would prevent them from modifying permissions on the DB but either the db_securityadmin or db_accessadmin roles could be granted as appropriate keeping in mind that it's possible for db_securityadmin to be used for privilege escalation but db_accessadmin may not meet your needs. As always, test the setup before deploying it to production to make sure it restricts what you want it to while allowing what you want it to.

If the goal is to prevent DB growth make sure autogrow is disabled on these databases.

share|improve this answer
add comment

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.