Matthew Flatt

27 July 2015

• SQL Source Control
• Database Source Control

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

2. Locate the Users’ permissions and role memberships ignore option near the bottom of the list and select it.
3. Click Save.
4. 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…

3. Un-check User near the bottom of the list on the left and click Save and close.
4. 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:

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.