15 July 2016
15 July 2016

Using SQL Compare with Dynamic Data Masking

One of the features that I hear many DBAs excited about in SQL Server 2016 is Dynamic Data Masking (DDM). This allows DBAs to centrally set a mask that is returned for a table column instead of the actual data. This means an application that wants to limit what information is returned to certain users can do so easily and consistently without relying on, or assuming, the front end application has implemented the feature.

Redgate SQL Compare works beautifully with DDM, detecting the differences in masks across tables and generating the T-SQL you need to deploy your DDM code to other databases. In this piece, we will examine how easy it is to use SQL Compare alongside DDM.

Working in development

The DDM feature can be added to new tables at design time or included in existing tables that are being modified. The syntax for DDM is in the CREATE TABLE and ALTER TABLE commands, as an additional property that is added to a column.

Note: There is no support in the table designer in SSMS as of the time this piece was written.

If I create a new table, I can add masking inline with a column definition like this:


In this case, I’ve used a default mask, which will mask the string with Xs. If this table already existed, I can alter a column with the ALTER COLUMN syntax like this:


In this case, I’ve used a partial mask, which will expose some of the values in the field, but replace others with Ys. (For more information on DDM, see Microsoft Books Online (BOL) or the Stairway to Dynamic Data Masking series at SQLServerCentral.)

When implementing a mask, there are multiple ways I can mask data. The masks in SQL Server 2016 are:

  • Default – works with most datatypes
  • Email – works with strings only
  • Custom – works with strings
  • Random – for numeric data

Each of these has its own syntax for configuration, but they are all implemented as part of the CREATE/ALTER TABLE syntax for a column.

Deploying DDM code

SQL Compare has been enhanced to include support for various SQL Server 2016 features, including Dynamic Data Masking. Let’s look at how this works in practice.

I’ve got a table in development, dbo.OrderHeader, where I’ve implemented DDM on a couple columns. The table designer doesn’t show DDM, but if I script the table, I’ll see this:


If I look in the production database, I see this code:


Now, I want to deploy these changes from the development environment to the production environment (or some intermediate testing environment). The trick is moving these changes. Obviously in development I implemented this in a manner similar to this:

However, that was some time ago, and I don’t have this script. SQL Compare makes it easy to regenerate this code with a quick comparison. I’ve created a project here that will compare the objects in my two databases.


When I click OK, SQL Compare will run the comparison and give me the results. I can see there are a few items that don’t match up in the databases, and a few that are in only one or the other.


I have the option to choose which objects should be changed, and I’ve selected only my OrderDetail table to deploy. In the lower pane, I can see the differences between the tables. There are some index differences, but the important part is that the DDM code is captured here.


When I click Deploy, I have a few options available. I am choosing the Deploy using SQL Compare option for this article. If you need to use this script as part of your deployment process, you may choose Create a deployment script instead. Note that I can still save a copy of the script and re-compare after deployment.


Pressing Next brings me to the Dependencies step. In this case, I have a Foreign Key (FK) between two tables, and the other table is listed, with the checkbox marked to deploy dependent objects.


This can be disconcerting, but if I click the dbo.OrderHeader link, I’ll get the comparison of the object in both the source and target databases. In this case, you can see below there are no differences, so nothing will be deployed.


The final step is to review the script and deploy the changes. In this case, I have a list of changes to the dbo.OrderDetail table. As I look at the script, I can see there are actually three ALTERs taking place here.


If I scroll to the right, I can see the details of the masking being applied to the two columns in the table.


The last step is to click Deploy Now to execute this script on the target database. Once I do this, I get a dialog to confirm my choice.


SQL Compare then performs the deployment (and saves a copy of the script). I can see the success or failure of each step.


After the deployment is complete, the last step is to recompare the databases, and that works. As you can see below, there is no difference detected for the dbo.OrderDetail table.


Did it really work?

Now to test the production database. I can first script out the table from production and see the code. In this case, the script is shown below, with the database name to the lower right (EncryptionDemo_Production).


I can also query the table as a normal user, one that has not been granted the UNMASK permission. When I do this, I get masked results. I also have a second query in this batch, as the db_owner, which has been granted UNMASK permissions.


Now watch a demo

I hope I’ve given you a good idea of how Dynamic Data Masking works in this walk-through. To see how it works at the desktop, on your PC screen, I’ve also created a video you can watch. It only lasts three minutes, so why not press the play button?

Smooth deployments with Dynamic Data Masking

The addition of DDM to SQL Server 2016 is a welcome feature I suspect many developers and administrators will want to implement. However, ensuring the correct masks are applied to the correct tables and columns is a tedious, error-prone task when performed by hand.

SQL Compare easily handles your DDM changes and ensures they’re migrated from one database to the next with the same settings, just as easily as you’ve come to expect every other code change detected and scripted by SQL Compare.

Try it now free or ‘Check for Updates’ under the Help menu in-product to make sure you’re on the latest version.

Tools in this post

SQL Compare

Compare SQL Server schemas and deploy differences fast.

Find out more

Share this post.

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

Related posts

Also in Hub

Tackle common database problems more effectively

While the database is sometimes seen as the blocker in the development process, it doesn’t have to be. With the right knowledge and tools, you can stop problems before they occur, help to speed up d...

Also in Product learning

Get a lot more value from your tools

From provisioning databases through to version control, automated deployments and monitoring, Redgate has a tool for every part of the database development process.

You probably have a favored tool b...

Also in SQL Compare

A whole new way to see differences in SQL Compare

In the latest release of SQL Compare, we’ve added a brand new way to examine the differences between two objects in your database.

Until now, SQL Compare has relied on the SQL difference viewer to ...

Also about SQL Compare

How to deploy a database plus static data using SQL Compare and SQL Data Compare

We can use the SQL Compare Pro command line with PowerShell to automate the process of creating a new build script for a version of the database, from the object scripts in the VCS. However, what if w...

Also about SQL Server 2016

Using SQL Compare with In-Memory OLTP tables

If you’ve read my simple assessment of SQL Server’s In-Memory OLTP tables on Simple-Talk and have decided to implement them, we're in a position to support your move. Redgate SQL Compare can help ...