on May 30, 2019
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:
- Concerns that development environments will drift so far from production standards that they will introduce risks to code quality
- 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.
REGISTER