Data masking is a feature that allows you to completely or partially mask selected data in a database. Access to unmask the data can also be granted or revoked for individual users. Data masking and anonymization have become increasingly important topics, especially with the introduction of GDPR regulations by the European Commission, which require greater attention to the protection of personal data.

This feature is available in SQL Server versions 2016 and newer and now I’ll provide a more detailed overview of this feature.

How Does Data Masking Work?

Masking in SQL Server is relatively straightforward. Unlike row-level security, which controls access permissions at the individual row level in a table, data masking focuses on entire columns. To mask a specific column, you use a masking function, and you can also allow unmasking of data for specific users. The basic process involves:

  • Identifying the columns in various tables that you want to mask
  • Applying masking to those columns. You have four masking options to choose from:
    • default – the attribute value is displayed as “XXX”
    • email – email addresses are displayed as
    • partial – you specify the number and position of characters to be masked
    • random – used to mask numeric values by selecting a random numeric value within a specified range
  • If you need to unmask data for certain users, you grant UNMASK permissions

Privileged users (sysadmins, db_owners) always see unmasked data. Masked data can be used for all operations as before, including filtering, etc.

Masking and Unmasking a Column – MASKED WITH

To mask a specific attribute, you can use the following SQL script. Data in the specified column will then be displayed as “XXX”.


To unmask a column, you can use the following SQL script:


Assigning and Revoking UNMASK Permissions – GRANT/REVOKE UNMASK

To allow a user or role to see unmasked data, you can use the following command:


To revoke these permissions, you can use the following command:


Permissions are granted or revoked at the database level. You cannot choose specific objects or columns for which you want to control permissions. Therefore, data is either masked or unmasked for a specific user for all objects in the database.
If you need to handle data visibility at the individual column level, you must use traditional access control, specifically Column level permissions – for more details, refer to this link.

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

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