DBAs: Stop Denying Sysadmin to Developers

I recent chatted with some folks who have a permissions problem in SQL Server. The permissions problem isn’t technical — it’s a process problem.

The issue is that these folks are trying to configure a build for their SQL Server databases using Redgate tools, but they aren’t allowed sysadmin permissions on any SQL Server instance in their organization (even in development environments), because of a policy set by the Database Administrators in IT.

Why do DBAs deny sysadmin permissions in development?

You may find this type of policy puzzling — and for good reason. After all, if you don’t trust developers to keep their own development environment running, why would you ever trust any code they’ve written enough to deploy it to production?

In that question is a bit of an answer: this policy occurs when there is a fundamental lack of trust between development and operations.

The policy that developers can’t have sysadmin rights in development has two main justifications used by database administrators:

  1. Concerns that development environments will drift so far from production standards that they will introduce risks to code quality
  2. Concerns that allowing any account sysadmin rights in development will lead to application accounts using sysadmin rights in production

Denying sysadmin rights to developers locks you into manually controlling environment drift — instead of managing it the smart way

If you’ve worked in IT or in developing changes for databases for a while, you’ve probably come across development database instances that are a complete mess — there are databases with odd names which maybe haven’t been used in a long time (but take up a lot of space), jobs failing, low disk space, and settings which resemble nothing in production. This messy type of development instance isn’t completely surprising, because developers need room to experiment.

Sometimes when the DBAs get called in to help support and instance like this, they respond by locking down permissions. If developers have to ask to create a database, we won’t get unexplained databases, and so on. The idea is to prevent the drift of these shared environments.

But treating developers like children won’t improve the quality of development long term. Removing permissions in development environments instead limits the ability of developers to experiment and automate, and limits the tools you can use to improve code quality.

How DBAs should fix a drifted shared development environment: blow it up

It’s true that nobody wants to support a shared development database environment. But here’s the thing: you shouldn’t even have that shared development environment. As Troy Hunt explains in “The unnecessary evil of the shared development database,” it makes a lot more sense for each developer to have a dedicated environment to work in.

One of the things that happens when you develop in a private environment and use standardized practices for your database code is that you automatically begin to control environment drift, by limiting routes for deployment.

In this world, there is no “wild west” shared development environment. Instead you have a workflow like this:

  • Each developer has a private development environment
  • Each developer checks their database code into source control
  • Branches in source control are combined with the private development environment to provide isolation / room to experiment for changes (in a way that will not impact other developers)
  • When code is ready to be merged in, automated processes build the code — proving that the code in source compiles properly and that all dependencies are met
    • Having sysadmin rights for the process building your code is helpful for flexibility — for example, you might want your build to create a SQL Agent job if it does not exist, etc
  • Automation also provides support for deploying that code to a fresh environment for review

This process emphasizes that development environments need to be able to be quickly reset / recreated whenever needed, and that doing so should not impact other developers.

This approach also forces developers to ensure that configuration changes are standardized and are handled by code as much as possible, as there is no ability for them to do manual configuration to make the build or review process succeed.

Most importantly, this approach reduces environment drift while also using automation to improve code quality.

But a key to making this work is allowing high permission right to the accounts which are carrying out the build and other automation.

What about the possibility of a “sysadmin free for all”?

The other reason this policy creeps up is the idea that developers can’t be trusted to follow the principle of least privilege, and if you give them sysadmin rights for anything they will use it for everything.

Let’s assume that’s a correct assumption for a moment. Giving developers lower privilege won’t actually fix the problem if they are always going to configure accounts to have the maximum permission allowed (“everything except sysadmin”).

This, again, is an issue of trust.

The better answer here is not to focus solely on what developer’s permissions are in the development environment. The answer is to focus on the permissions that application service accounts have, and to examine when and how code reviews can make sure that the roles used by service accounts are following the principle of least privilege.

To survive, DBAs need to stop being the TSA

We increasingly live in a world where the ability to deliver changes in software quickly, without impacting the user experience, is a core requirement to doing business.

However, we still live in a world where database administrator groups create policies that slow down development.

Worse, these policies also prevent the effective use of tools — like automation for builds and deployment of code to fresh environments for review — that improve code quality and reduce the risk of environment drift.

If you were a business owner and were investigating how to speed up time to market for your products, would you want your DBA team to be creating and enforcing policies that slow down releases and don’t improve quality?

Or would you be tempted to replace them with a different team, who would invest in using automation to improve code quality as well as improve release tempo?

Don’t make your organization choose. As DBAs we need to leave our obsession with policies behind, and focus on how to get what we truly want: high quality deployments and efficient management of our environments.

Previous Post
What’s Unique About SQL Saturdays: Nearly Everyone Interacts with Other People
Next Post
A letter to my 20 year-old self

Related Posts

No results found

11 Comments. Leave new

  • Tommy Bollhofer
    May 30, 2019 8:07 am

    Worth noting that there are various ways to accomplish this via custom server roles as well. We can grant alter any database, login, view server state, etc. to a series of custom server roles with varying levels of access, depending on what is needed without compromising access to sysadmin. Our developers are able to do everything they need to to build and deploy, and our security team is happy. Win/Win.

    Reply
  • I’ve never been in the position of making the who, what, when, where, access call as that has been determined by SOX policies or (now) SOC 2 policies, both of which are management driven. I periodically give myself “self-tests” as in “If X occurs, what is the response?”. I actually write down my response steps in my daily journal.

    Reply
  • One concern that still remains for me (DBA talking) is that of developers backing up databases off of a shared QA or DEV environment and “walking away” with sensitive data. I realize that this (again) raises the issue of shouldn’t you be trusting your developers who already have access to a lot of data, but it is a legitimate concern.

    Reply
    • I think this is a really valid concern — but limiting permissions doesn’t fix it. Even with read access, I can easily walk off with the sensitive data. Organizations generally do need to provide pretty broad access to environments like QA, so a single successful phishing attack on a developer could mean someone outside the organization getting access to that data.

      It feels like it’s easier than ever now to copy sensitive data with only read access, actually, due to the ease of Excel querying databases and Notebooks in Azure Data Studio making me not even need to copy and paste.

      The way forward here is to remove or mask the sensitive data. This can be automated with vendor products (Redgate has a product for this, as do other vendors), custom code, or open source tooling like dbatools. Alternatively, some people also simply generate the data for some tables/databases if masking sensitive data isn’t allowed by their organization.

      Reply
  • Chris Lumnah
    May 30, 2019 10:59 am

    One thing to note as a point of correction. As a DBA Team lead, it was not my policy to not give sysadmin to developers. It was an audit and security policy that no one could have sysadmin or local admin to a SQL Server except for the DBAs and the Domain Admins. It was the DBA team’s role to enforce the audit and/or security policy. Additionally, those development servers were shared among several developers not all in the same application team or department. Thus giving them sysadmin would have one impacting another. It is not feasible in all environments to give each developer their own environment that is isolated. In our environment, we would not give sysadmin, but we would give db_owner access to the developers. This would give the developer the permissions they needed to do their job, while also keeping them out of another team’s work and affecting them.

    Reply
  • If you need to be concerned about drift in development, you’re doing it wrong. Databases should be changed via source code. That should always be the master.

    A developer’s database is for working out and testing those source code changes … only.

    Reply
  • Kevin Boles
    June 2, 2019 10:29 pm

    If you have sensitive data that shouldn’t be taken off site by people, then you cannot give those people ANY ability to access that data. Problem solved, and there are various ways to accomplish thar starting with proper permissions and for developers probably ending with data obfuscation.

    I’m with you Kendra, and in fact just recently got another client to agree that this should be done.

    Reply
  • Gerald Britton
    June 3, 2019 3:49 am

    One really simple option is to run sql server in a container that is composed from one of the base images and autobuilt from git. The container could be local or on a Dev server. Either way, it’s isolated and throwaway.

    Reply
  • Richard Garrett
    July 31, 2019 12:59 pm

    The premise of the argument is summed up with this sentence: “After all, if you don’t trust developers to keep their own development environment running, why would you ever trust any code they’ve written enough to deploy it to production?”

    But if you take out “developer” and substitute it with ANY other IT role that might need to touch a database at some point, the statement validity quickly starts to fall apart. Or, let’s apply that logic and swap the roles of developers wanting SA on database environments and let’s say that DBA’s was administrator rights in the application and code environments. The “trust” part is certainly there but does the reasoning still make as much sense to developers? How about network admins? Should a DBA/Developer/Report Writer/Analyst be given Domain Administrator because the trust is there and it would make their life easier?

    We all have our domains that we operate the most comfortably in and are our responsibility. We should never assume that because our motives are pure, it makes it OK or gives us the right to do things in other domains.

    Reply
    • I somewhat see what you are saying, but you’ve oversimplified the point of the article.

      My point is that centralized, shared development environments that are tightly controlled by IT hamper innovation and the ability to deliver value to customers quickly. The lack of trust is only a part of this — these processes tend to stick in place due to a lack of trust from the IT point of view. Sure, there are other factors that play in, such as a traditional fear of change from IT staff.

      The bigger picture is that with modern technology and software development tools, there are many ways to set guardrails in place to protect customer data and company IP while fostering innovation. Organizations are very hungry for innovation, as competition in an internet world allows small companies to quickly take market share from established brands. It’s also hard to hire good engineers, and leadership knows they need to make the most of of the engineering time they have.

      Combined with this add in the rise of containerization and growing success of people who embrace new ways of working / infrastructure as code.

      Together, this all means that there will be fewer and fewer opportunities for folks who stick with old processes on the premise that “we all have our own domains that we operate the most comfortably in.” Conversely, there are many new and interesting career options out there for people who are interested in finding ways to improve code quality and speed up development safely as part of their specialization.

      Reply
    • PS: also I would like to thank you for your comment! Whether or not you agree with my response or the article, your point of view is valuable and prodded me to think a bit more about what I was trying to get across, and I truly appreciate that. It’s one of the best values of blogging.

      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu