Friday , June 21 2024

SQL Server 2016: Dynamic Data Masking


I tested the Dynamic Data Masking feature in my SQL Server 2016 test environment, and I want to share the result with you. I used the PhoneNumber field in the AdventureWorks2012.Person.PersonPhone table for the test. Using the Dynamic Data Masking feature, I mask 3 characters in the middle of the values in the PhoneNumber field created with the NVARCHAR (25) data type with the following commands.

Before masking:

The commands I use for masking:

With this command, we don’t change the data, we just mask it as abstract. All users who do not have db_owner and sysadmin and do not have UNMASK privileges see the data as follows:

If I wanted to, I wouldn’t show any data about phone numbers and / or I would use as many “X” numbers as I’d like, and I couldn’t give any idea about how many characters would be.

You can also remove this feature in a masked area by using the following command:

To use the Dynamic Data Masking feature, the Compatibility Level setting of the database (SQL Server 2016) is required. For example, for a database with a Compatibility Level setting of 90 (SQL Server 2005), this feature is not available.

Although I don’t expect a big negativity in terms of performance, I don’t know what impact it will have on performance; But its use is quite practical, the application side does not require any modification. The only change on the management side is to define the relevant field as MASKED with the command I mentioned above and to grant UNMASK authorization to the required users.


About Ekrem Önsoy

The original article was written in Turkish by Ekrem Önsoy and translated to English by dbtut with the consent of the author. The copyright of the article belongs to the author. The author shall not be liable in any way for any defect caused by translation.

Leave a Reply

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