How can I deny a user the ability to Alter DB files and options but still allow schema and data modifications?

Question

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?

Answer 1

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.