1

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.

  • 1
    I would use this as an opportunity to get an official project on the books that deals with automation. You can target him as the pilot so he has first access to getting changes pushed out via a CI/CD process after code has been approved. Brent Ozar has a good post with some sa-related talking points. – LowlyDBA Oct 12 '17 at 13:24
2

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

1

This is a sad commentary on the state of software development and deployments that rely on an RDBMS such as SQL Server. The development and/or DevOps teams should know what level of authorization is needed by applications, both at runtime and at deployment time. It's very important to make the distinction between these two. Unfortunately, development and/or DevOps teams never figure this out because {reasons}. So here you are...

You've mentioned that you are familiar with fixed server roles and fixed database roles. I don't want to insult your intelligence, but I think it's worth mentioning for those that are not familiar. At the server/instance level, the CONTROL SERVER permission is a much better option than membership in sysadmin. At the database level, I would never let a non-dba own a database. They connect as dbo -- DENY permissions have no effect. You can deny permissions to a database user that is a member of db_owner -- this is also a better option than membership in sysadmin. But it is still usually overkill. I generally prefer to give a database user membership in db_ddladmin, db_datareader, and db_datawriter. Additionally, I'll GRANT EXECUTE, CREATE SCHEMA, and VIEW DEFINITION. Based on your needs, your mileage may vary. Another thing to note: you cannot DENY permission on DBCC commands. Members of db_owner can do a lot with DBCC, and to a lesser extent, members of db_ddladmin can too.

After all of that, I've still had to give sysadmin authorization to non-DBA types on a regular basis for deployments. In those scenarios, the requestor had to submit their request to a Change Advisory Board for approval. Membership in sysadmin was temporary and I always set up a SQL Agent job to remove group membership after an agreed upon amount of time (usually one week). I did this enough times that I wrote re-usable code to quickly create the necessary job, job steps, schedule, etc.

Moving on...

Whether it's temporary or permanent, you still have to deal with non-DBAs that have elevated permissions. SQL Server has some built-in features that can help you handle events that you might not want to happen:

DDL Triggers: these are a great way to handle certain SQL Server events synchronously. Maybe you just want to know an event happened. Grab the EVENTDATA() and send an alert or log the info to a table for later inspection. If you're more hands on, you can inspect the EVENTDATA() as events occur and optionally ROLLBACK the bad stuff. It's really powerful. I kept an eye on ALTER_INSTANCE, ALTER_DATABASE, DROP_DATABASE, and a raft of authorization-related events. (Note: ROLLBACK doesn't work with the ALTER_DATABASE event.)

EVENT NOTIFICATIONS: some events can't be handled synchronously with DDL triggers, but they can be handled with event notifications. ROLLBACK is not an option, but you can at least grab the EVENTDATA() for post-mortem analysis. This option involves Service Broker, and the learning curve to get started is much higher than working with DDL triggers. I think it's worth it, though.

There's nothing to prevent a member of sysadmin from "sidestepping" a DDL trigger or Event Notification (other than their own ignorance). But that's a severe offense IMO, worthy of disciplinary action.

Another feature you may want to look at is Policy Management. I've not used it myself, but it appears to be quite powerful and flexible. It may also be a bit easier to use as compared to DDL triggers and Event Notifications. I'll let you be the judge of that.

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.