One of the most common issue you can find when source controlling the database is about the security. How to manage the users and the related permissions?
If you use to apply permission to users and to assign users to the database, this can be a problem, especially when you are in the deployment phase (or else when getting latest versions from the source control). Let’s see these two scenarios:
Development scenario
We have two machines, with two SQL Server instances installed on each one. Both computers are connected to the same remote source control store. The first developer (let’s call him Mike) adds SQL Server users (sql authentication), while the second one (Alex) uses the Windows integrated security. These two developers often need to add permission to their users.
Assume that users are not member of the sysadmin role.
If, for example, the developers want to give the SELECT, INSERT and UPDATE permissions to a HumanResources.Employees table, they need to execute the following command:
GRANT INSERT, UPDATE, SELECT ON HumanResources.Employees TO 'Domain\myWindowsUser'; -- Windows
GRANT INSERT, UPDATE, SELECT ON HumanResources.Employees TO myDatabaseUser; -- SQL
Both Mike and Alex will see the user checked with a blue icon (which means that the object has been modified):
After the checkin of this changeset, what happens when the developers will get the latest source control version of the database?
That said, everyone will have to get the user of each other. Red Gate SQL Source Control will describe one of the change as in the following picture:
This means that Alex has to accept the “myDatabaseUser” user while Mike has to get the Windows credential (that is not so comfortable, especially if the domain user does not exist). Without these information the get will not run successfully, since permission cannot be applied to the objects. Additionally, the sensitive data, like the password, will be changed:
After the get of the latest version, Alex will find the new user under its database security:
Deployment scenario
When the developers are ready to deploy the changesets, “myDatabaseUser” and “myWindowsUser” users will be in the package. this can be dangerous for the target environment (and also wrong). Like when getting the latest versions from the source control, thew deployment process will try to apply users to the test/staging/production environment. How to manage this trouble?
Solutions
Both the described scenario have near the same issues. However, the development is slowed down while the deployment environment can be broken. Here is how we can solve this problem.
First step – migrate from users to database roles
A database role is an item with which you can assign permissions without considering users. Thus, users will be added to the role, right on the environment without affecting any changeset. A database role is a set of users, like a domain group on the domain controller. Suppose to have a role called ApplicationAccessRole. When we apply the permissions to it, the DCL statement becomes:
GRANT INSERT, UPDATE, SELECT ON HumanResources.Employees TO ApplicationAccessRole;
Alex and Mike create the database role, using the “CREATE ROLE” statement and, then they assign permissions with that GRANT. After that, they add their users to the database role:
EXEC sys.sp_addrolemember @rolename = 'ApplicationAccessRole', @membername = 'myDatabaseUser'; -- SQL Server 2008R2 and older
OR
ALTER ROLE ApplicationAccessRole ADD MEMBER myDatabaseUser; --<-- from SQL Server 2012
SQL Source Control will show the following edit:
We are near to the end, but, as we can see, the “myDatabaseUser” is involved in the change, and we want to avoid this.
Second step – Ignore users
You can change SQL Source Control general options on the “setup” tab. Check the “Ignore users and role membership”:
When Alex and Mike change the option, a new item will be added on the changeset, called Database Comparison Options. They have to checkin just that option, in order to see the new behavior:
Cool! The user is not shown anymore. However, it’s still possible to add users manually on the database.
Third step – filtrer out the new users
SQL Source Control allows us to apply filters on database objects. You can right click on “Other SQL Source Control tasks” on a database, and then “Edit filter rules..”. A view like the following one will pop up:
Alex and Mike will remove the “User” check, and they will save the change and check in just the filter. After this, they will not see any user in their changeset.
That’s it.
Stay Tuned! 
