SQL Server SQL Terminologies

SQL Server 2016 dynamic data masking – a preface

Dynamic data masking is one of best security attribute introduced in SQL Server 2016 Community Technology Preview 3. This is a security mechanism which covers the sensitive piece of information in the output of a user query over a set of defined column. This feature is very easy to implement with Existing code as this is applied at the Query output level. Main motivation to use this feature is to restrict visibility of critical data from unauthorized viewing. This is a security mechanism which hides the sensitive piece of information in the output of a user query over a set of defined column.

e.g. an application might show a Credit Card number and cover everything except the last three characters. Rather than showing 891-634-932, it would show XXX-XXX-932. Since this data obfuscation is performed at the output level base on the user’s permissions and type and it’s not masked at the storage level, hence it is not a complete solution to secure the data. Dynamic Data masking is totally different from encryption feature. But this can be considered as part of the Security policy.

Dynamic data masking is very helpful in some of the situations like

  • As self explained, It can be used in some situations where Data obfuscation is mandatory as per the Government regulatory like Sarbanes-Oxley, PCI DSS, HIPAA etc. According to the best practices it’s always very important for Financial and Insurance organizations to protect the sensitive and personal information using best practices.
  • Apart from this Dynamic data masking can be utilized for Developers. We often get numerous requests from development teams to share the data for testing purpose, in those scenarios while sharing information we must obfuscate the sensitive data using some mechanism. Using Dynamic data masking real time masked information can be shared with the developers for more realistic testing.

SQL Server Dynamic data masking is available in four different formats based on the data types.

Default- Fully masked data as per the data types of the selected fields.

example: ALTER COLUMN City ADD MASKED WITH (FUNCTION = 'default()') 

Email – Using this method we can apply masking on the email data of the users.

example: ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()') 

Custom String – As per the name, this method can be used to mask the starting characters and last characters as per the custom requirement.

example: ALTER COLUMN [Mobile Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')

Random – This function is used to replace number columns with random values.

example: ALTER COLUMN [OrdrerNO] ADD MASKED WITH (FUNCTION = 'random(1, 5)')

In this below examples, I will show you how to setup Dynamic data masking in SQL Server 2016.

1. Create a dummy table using below script and insert some data records.


2. Create a test login and database user to verify the data masking and grant read permissions on the database.

USE master
USE [Adevntureworks2016CTP3]
Create USER [Paul] FOR LOGIN [Paul]
ALTER ROLE [data_reader] ADD MEMBER [Paul]

3. Connect to the database and verify the database permissions.
12Since no data masking is applied, data is visible with read only permissions to the users.
134. Let’s use Dynamic data masking to hide the email address information of the mycustomer table using alter statement.

Use the below script to mask the email address column data from the users which have read permissions on the database.

ALTER TABLE mycustomer

5. Now run the select statement using test user (PAUL) which has read permissions on the database and we can see that the data masking has been applied on the Email address field.

146.  There is another option available to mask email addresses as other columns using partial function. In this example we will drop the masking on the EmailAddress column and will reconfigure masking.

ALTER TABLE mycustomer
ALTER TABLE mycustomer

7. Now select the data using same read only user, data is masked for EmailAddress field

158. In this example we will perform masking on PhoneNumber column to apply data masking using below command.

ALTER TABLE mycustomer
ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION =’partial(4,”-XXX-“,4)’)

9. Quering the data using read only user will display the PhoneNumber field masked as below.

This is how you can implement dynamic data masking without making major changes in the Application logic. Since this feature has been recently introduced by Microsoft, Hence there are few restrictions or limitations as following

  • Data masking cannot be applied on Encrypted columns, in Filestream and on a Computed column.
  • Some of the data types are also not supported e.g. max data type.

Microsoft is continuously working towards the enhancement of SQL Server features and personally I liked this feature very much, using this feature there is no need of the Data Obfuscation third party tools.


Technical Editor

About Author

SQL Server DBA, Trainer, Blogger, Author

1 Comment

  1. Avatar

    Tobias Armstrong

    March 8, 2016

    I think that being able to mask data is an awesome development in the workplace. In a day when it seems like privacy isn’t quite what it used to be, having software specifically designed to help support customer privacy seems like a killer idea. Thanks for walking through the steps needed to actually make data masking a reality!

Leave a comment

You may also like

Installations & Configuration SQL Server Troubleshooting

SQL Server Database mail common issue

Problem: SQL Server Database Mail common issue We always use SQL Server Database mail feature to create email notification for
Installations & Configuration SQL Server Technology Virtualisation

How to use Microsoft Virtual Labs for Practical implementation

One of the important questions often asked by my fellow mates is how we can implement practical scenarios without configuring