Source controlling database users
Where developers have their own databases, you need to ensure the user accounts are configured identically across all development and test server, or you'll inevitably hear the phrase “…but it works on my machine”. Alex Yates describes, strategically, how this might work.
How should you source control database users?
This topic came up a couple of times recently. Once at the inaugural Redgate database source control training day and once or twice at SQL Saturday Exeter when, somewhere in between the foot jenga, pirate hats, and explosive Phil Factor unmasking (if you believe it?), I found some time to talk to people about database lifecycle management (DLM).
We use version control for many reasons. It is our source of truth, the source of our deployments and the source of answers to questions about how stuff has changed, who changed it and (crucially) why it was changed. We also use it as a communication tool and to provide consistency among development environments. But this variety of use cases sometimes result in contradicting requirements.
Let’s consider, for example, security. Let’s also assume for the time being that each developer has their own dev copy of the database. What are the requirements for roles and users?
Well hopefully we can have a consistent set of roles across all environments. This will help to keep things simple. But the users assigned to those roles will be different on development and on production databases. Developers need to be free to make schema changes on their dev databases but they should certainly not have the same level of access to production databases.
Where developers have their own databases, it would be wise to ensure users are consistent. If developers, applications or other systems are expected to have their own user accounts these should not differ between dev databases lest, at some unfortunate time in the future, we wish to hear those too often iterated words: “…but it works on my machine?”
Version control is a great place for this information to live. We can audit changes to users over time and we can ensure that dev environments are in sync with version control.
However, here comes a conflict with a different use case for version control. In general we want to ensure that not only dev databases, but also test and production databases are consistent with version control too, but we don’t want to put our dev user accounts onto the production system.
When dev users are deployed to production this can at best be seen as an honest mistake but at worse it can be seen as an act of fraud – and this can lead to some very uncomfortable conversations or worse.
So we want to ensure that users in our dev environments are consistent but we have a different set of users that we want to maintain in production. We also want a way of tracking changes to user accounts over time.
Essentially, we want to filter out some of the objects from version control, depending on the environments we are deploying to.
So where should we put our filters?
One approach is to ignore users entirely from your main version control repository. This might work fairly well where all developers are using the same dev database. In this sort of environment the need to ensure different dev databases are consistent vanishes and production databases are almost certainly to require different user accounts anyway. But it would still be useful to track changes to users here so perhaps it is worth thinking about how this could be done separately from your main version control repository.
Another approach is to include all users in version control but to add filters at deployment time. This way (where developers have their own databases), all users are shared between dev environments but they do not get deployed up your pipeline. If you deploy manually this can become an extra step that needs to be handled consistently.
You need to be careful with this. I discussed earlier the consequences of forgetting to strip out the users when deploying to production databases and accidentally deploying a few extra users can easily go unnoticed as it probably won’t break anything.
You can get around this risk of human error by automating a deployment process that includes the filter. (Note that I’m talking about automating the process only – not the trigger. And I still recommend letting humans review deployments and choose deployment schedules.)
Do you do something similar to either of the strategies I’ve described? Do you have any other ideas on how to handle different configurations for different environments within an automated or semi-automated DLM process? If you have anything to add or if you have any questions please leave a comment or pick up the conversation with me on twitter!
If you are a Redgate user you should know that all the Redgate database version control and deployment tools include functionality to add filters. For example, you can find SQL Source Control filters documentation here and SQL Compare filters documentation here. If you want to automate deployments using filters with SQL Release you’ll find documentation here. If you use other tooling to manage your database version control or deployment process you may find similar functionality but I cannot talk for all and naturally I understand the Redgate tools best!
This article was originally published on the working with devs blog on 26 April, 2015
Was this article helpful?
Tools in this post
The industry-standard tools for SQL Server development and deployment.