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?

  • Why not grant them permissions at the schema level instead of giving them db_owner? – user507 Oct 10 '12 at 16:32
  • See this dba.stackexchange.com/a/6921/630 please – gbn Oct 11 '12 at 7:00

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.

| improve this answer | |

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.