High Level Permissions For an Application Owner I'm the primary DBA for a 100-user data warehouse with approximately a dozen developers and analysts regularly contributing additions to the data model and codebase (mostly stored procedure and fact table additions). I'm following a relatively traditional deployment process focusing on regularly-scheduled code reviews and deployment windows with change control tickets twice a week with me an a colleague DBA handling the deployments. I've used SQL Audit, default trace data and RedGate DLM Dashboard to keep tabs on all the schema changes.

The tempo has increased steadily in the last two years and some of the lead analysts would like to go towards a DevOps deployment method using automation. The director and app owner is the most accomplished developer creating assemblies for the advanced ETL,  looking to perform deployments independent of me and my colleague, asking for sysadmin privs when we're backlogged or short-handed during vacation.

I've declined requests to share sysadmin privileges with the director because of the risk of setting a precedent for other developers, the liability if access is opened too broadly in error and the risk of a deployment error which will get me paged at 3am when I'm off-call. On the flip side, I'm aware of the business pressure on the developers to get new content to market faster, even if it's not properly tuned. Finally, the political and departmental element of reporting to the director is present; it's easier to say 'No' if you're a DBA in a separate unit, but harder if the director signs your paycheck.

I've told my director in the past that in the event of a breach or system failure, all the sysadmins need to be at the IT Security board of inquiry to explain what happened, which has given him pause in the past, but the business risk of delaying content is getting stronger. What options can I offer to the director that go short of sysadmin rights that give him more latitude to deploy but have the proper level of auditing in depth? I'm familiar with the various server and database fixed roles, but I'm looking at departmental protocol options as well.
A short answer

I had similar issues when I was a BI DBA, trying to control deployments without blocking the business needs.
And generally prevent yet another badly coded csv-parsing function to add to the current 13 or so already pushed to live.


You don't need sysadmin to deploy
Depending on exact need, you need one of ddl_admin/ALTER SCHEMA/db_owner


This way the scope of a screw up is limited to the database and/or schema.

ALTER SCHEMA is very useful for limiting changes and I've done this before.

Do these folk needs to modify security for example? Most likely not.

I'd also implement some kind of DDL auditing (I have a server level DDL trigger) that writes to a DBA-only database. This way all DDL is logged