27 July 2015
27 July 2015

Source controlling your database permissions

The problem

A common question we encounter when working with SQL Source Control is about database permissions. Different environments require different permissions, so should you just omit them entirely from Source Control? Or perhaps include only permissions for your production environment?

Permissions are extremely important, so not source controlling them at all doesn’t seem right; but what happens if the Windows Authentication user you’re trying to add doesn’t exist, or isn’t accessible from all your environments? In this case, the deployment or Get Latest operation in SQL Source Control will fail.

The solution

The solution I’ve successfully implemented with customers is to use a combination of database roles, options, and filters in SQL Source Control and source-controlled post deployment scripts to add the required users to the correct role for each environment. This covers all the bases. Our permissions are source controlled with the database, and we can deploy to any environment without issues. Updating a local development database with a Get Latest in SQL Source Control will now work, whether users are available or not.

To set this up, we need the following steps:

  1. Setting the Ignore users’ option in SQL Source Control
  2. Adding a User filter in SQL Source Control
  3. Moving to role-based permissions for your database
  4. Creating your post deployment permissions script
  5. Executing your script after a successful deployment

We assume, that SQL Source control is already set up and in use. If this is not the case, worked examples can be found here.

Setting the Ignore users’ option in SQL Source Control

To avoid user permissions and role memberships being committed to source control, we need to set the Ignore users’ permissions and role memberships option in SQL Source Control. This is the same as the SQL Compare option. More information on this option can be found here.

The option needs to be set and then committed to source control so it is available to all team members. To do this:

  1. With your database selected, go to the SQL Source Control window in SSMS and choose Setup
  1. Locate the Users’ permissions and role memberships ignore option near the bottom of the list and select it.
  2. Click Save.
  3. On the Commit changes tab, refresh and commit the Comparison Options Edit change with a suitable comment.

When deploying from your SQL Source Control repository or using SQL CI, be sure to specify the Ignore users’ permissions and role memberships option to prevent the permissions and role memberships from being built and deployed.

If deploying using SQL Release, this ignore option is specified by default.

Adding a User filter in SQL Source Control

Like the Ignore users’ option, we now want to filter out any User objects from being committed to source control, as well as ensuring any already in source control aren’t deployed. To do this, add the filter and commit it to source control:

  1. With your database selected, go to the SQL Source Control window in SSMS and choose Setup
  2. Under Options just for this database, choose Edit filter rules…
  1. Un-check User near the bottom of the list on the left and click Save and close.
  2. On the Commit changes tab, refresh and commit the Filter New change with a suitable comment.

When deploying from your SQL Source Control repository or using SQL Release, be sure to filter out Users or use the Filter file which is now in your repository and database package.

Moving to role-based permissions for your database

Using role-based permissions gives you just as much functionality as assigning permissions directly to a database user.

If you do have specific user permissions on objects, simply create a custom database role or roles, assign these permissions to your new role(s), and add your user as a member of the role in a post deployment step.

Creating your post deployment permissions script

Once the database has all the necessary permissions added to different roles, after a successful deployment you need to add the logins, users and role memberships if they don’t already exist. Here’s an example script for a single user:

Example Script

Create a separate SQL script file for each environment and save them using a name you can later identify during deployment. For example: Permissions_Production.sql and Permissions_Staging.sql.

Commit these files using your regular source control tools to somewhere other than the SQL Source Control root. You can find the root by looking at the Repository value on the SQL Source Control Setup tab.

Executing your script after a successful deployment

Once you’ve created these SQL scripts with environment-specific names, you can easily execute them from your continuous integration tool or package them into a NuGet package for use by your release management tool. Using a release management tool, variables can be used to replace the environment part of the SQL script name, executing the correct script based on the current environment.

Tools in this post

SQL Source Control

Connect your databases to your source control system.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Merging a Conflict with SQL Source Control and Beyond Compare

    SQL Source Control (SoC) plugs directly into SQL Server Management Studio (SSMS) and is built with the singular purpose of providing an efficient interface between each developer’s local, working copy of the database, in SSMS, and the source control repository. It allows each developer to work freely, on their own sandbox database, committing tested changes

  • Article

    Versioning an existing database using SQL Source Control

    A while ago, maintaining a reliable database change history was an ongoing problem within our development team. It seemed we were forever asking questions such as “Who made this change?”, “When was the stored procedure last edited?”, “When was this view created?”, and so on. Our initial approach was simply to store a list of

  • Article

    A strategy for implementing database source control

    Much has been written on the benefits of having a database under source control though many articles are clear on “why” but conspicuously vague on “how”. Prior to our organization’s decision to embrace Linux and other open-source technologies, one of our development teams had notable success using Redgate SQL Source Control for a data mart

  • Article

    SQL Source Control v6 now supports SQL Server 2017, SQL Graph, and TFS 2018

    SQL Server 2017 went on general release a couple of weeks ago and the latest version of SQL Source Control offers full support, as well as support for Team Foundation Server (TFS) 2018. This brings a whole host of benefits and opens up cross-platform workflows, encompassing both on-premises and cloud, through connections to SQL Server instances

  • University

    Take the SQL Source Control course

    This course takes you from installation all the way up to getting the most out of the advanced operations in SQL Source Control to help you set the foundations for CI/CD. Learn how to link a development database to your source control system, commit changes into a repository, produce a data deployment script, and more.

  • Forums

    SQL Source Control Forum

    Connect your databases to your source control system