THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rick Heiges

News about SQL Server and the SQL Server Community

Update on My Dynamic Data Masking Article - Behavior Change in CTP 2.4


Recently, an article of mine was published by on the site titled “An introduction to SQL Server 2016 dynamic data masking” which can be found here.  I encourage you to read the article in order to get the most out of this blog post and to better understand this feature.  This article was based on the SQL Server 2016 CTP 2.2 release.  Since CTP 2.4 was recently released, I decided to run my script from the article and look for behavior differences. 

The Difference

Everything in my script worked the same except for where I test using a “SELECT INTO” approach to get around the data masking.  Here is the code snippet and result from the article.

Execute as user = 'TestUser';

Select * INTO dbo.maskemployee from dbo.employee;



Grant select on maskemployee to TestUser;


Execute as user = 'TestUser';

Select * from dbo.maskemployee;




When I ran this portion of my script in CTP 2.4, I was happy to see that the security hole had been addressed.  The “SELECT INTO” now creates the maskemployee table with the masked data INSTEAD of the real data.  On a side note, I also ran this script in CTP 2.3; the behavior was the same as CTP 2.2.

What about Azure SQL Database?

The Azure SQL Database preview was also recently updated.  I am happy to report that the above behavior is consistent with that outlined above.  One other note that I’d like to make about Dynamic Data Masking in Azure is that there are now suggestions for columns to be masked and also suggestions of what type of mask to use when navigating the blades.  I think that this is a good idea for those just starting to play with this feature, but that it will most likely go unused once the user is more experienced


As SQL Server 2016 gets closer to release next year, the CTP releases show progress on how the team at Microsoft is moving forward.  I imagine the team is starting to move into the “fit & finish” phase of some of these features.  I continue to look forward to see what’s next.


Published Tuesday, October 6, 2015 11:59 AM by RickHeiges

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment

Privacy Statement