Basics of Using Dynamic Data Masking

By | In SQL Server | September 10th, 2015

Basics of using Dynamic Data Masking with SQL Server 2016When SQL Server 2016 was released, I was interested in finding some of the cool capabilities as part of the release. In this blog, we will look at a simple example. Data Masking is a security feature which hides or obfuscates sensitive data from certain users. Adopting a data masking solution can enable database owners/administrators to raise the level of security and privacy assurance of their database – in addition to meeting compliance requirements with various security and privacy standards.

Dynamic Data Masking or Real Time Masking

This refers to a scenario meant to protect a production database, by returning obscured data on queries performed by certain users, as per defined by the masking policy. The actual data on the database is not changed. This means that ‘privileged’ users still get results with the actual data, while other users only get access to data to which they are entitled – again, based on the defined policies. Naturally, this implies that the determination of what data to return is done on the fly, in real time.

Data Masking in Action

I have started to experiment this feature on a CTP2.2 and I have not used the trace flags that you see on the internet. We will do the following:

1. Create a demo database for use
2. Create a table with a number of columns
3. Create the masking with different options
4. Create a login/user which will have low privileges
5. Check the effect of data masking

Now that the basic building blocks have been done. We will next create the user for our experimentation and see the effect of data masking on them.

The output for above will look like this:


As you can see, SQL Server 2016 introduces a powerful feature where privacy and data leakage of sensitive data for organizations can be taken care. I am sure there are compelling reasons where I see developers use this capability inside their application that it is worth a try.

Stay tuned for upcoming blogs on some of the new features of SQL Server 2016 and my impressions of the sneak peek from Microsoft SQL Server 2016.

Contact Us
Eric Russo
Senior Vice President of Database Services
Eric Russo is SVP of Database Services overseeing all of Datavail’s database practices including project and managed services for MS SQL, Oracle, Oracle EBS, MySQL, MongoDB, SharePoint and DB2. He is also the Product Owner for Datavail Delta, a database monitoring tool. He has 21 years’ experience in technology including 16 years in database management. His management success and style has attracted top DBAs from around the world to create one of the most talented and largest SQL Server teams. He has been with Datavail since 2008: previous to that his work experiences include DBA Manager at StrataVia, Senior Web Developer at Manifest Information Systems and SQL Server DBA at Clark County, Nevada.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).