How To Implement SQL Server Dynamic Data Masking?

👤 Andrew Jackson | Published: May 24th, 2025 | SQL | 4 Minutes Reading

SQL Server 2016 added a new protection feature known as Data Masking in SQL Server. It allows user to limit the sensitive data by masking it to unauthorized users. User can hide the sensitive information without changing the data of the database, since data masking is applied to the query result. It allows users to mask the sensitive column of a table present in SQL Server 2016 Database. Dynamic Data Masking in SQL Server Database is useful for regulatory compliance, as a protection layer that may be demanded by the government or by any other industrial body.

For example, a support person may identify their customers by their credit card number or by social security number. However, those numbers are exposed partially to the support person in case of Data Masking. data masking can be applied here to mask all the numbers except the last 4 digits. For example an executive can view the credit card number as XXXX-XXXX-XXXX-1234.

How to define Dynamic Data Masking in SQL Server?

You can define the Dynamic data masking rule on a column to protect your sensitive data. There are mainly four types of SQL Server data masking that are available for SQL Server 2016. They are: Default, Custom String, Email and Random.

Function	Description
Default	You can apply full mask according to the data type of fields. XXXX is for string data type. i.e, char, varchar, text etc. A ‘0’ value is for numeric data types such as int, decimal, float etc. ASCII value0 is used for binary data types. 01.01.2000 can be used for date and time datatypes
Email	All the letters of emails are masked except the first letter,@ and the suffix .com. For example sxxxxxxxxxxxx@xxxxx.com
Custom String	Expose first and last letters and masking is performed on middle part. For example Prefix, MASK, Suffix
Random	Random masking can be used for any numeric type, to mask the real value with random values.

Benefits of MSSQL Data Masking

This security feature helps users a lot in securing and protecting the data stored in the database by the users. We will now discuss a few of these benefits one by one to get the concept more clearly.

Protects the confidential data from any unauthorized access or any malicious activities.
Implementation of this protective layer is much simpler than other methods.
MSSQL Data Masking doesn’t impact the data storage or the structure, making it a convenient option for privacy and security.
The method also supports role-based access for enhanced database security.

Limitations of MS SQL Server Dynamic Data Masking

Dynamic data masking rule cannot be applied on following column types:

COLUMN_SET
FILESTREM
Encrypted columns

Setting Up Dynamic Data Masking

Step 1: Create a new database for masking Demo. If masking Demo database already exists then drop the database and create it again.

Create new database

Step 2: Create Table. Add different data types of columns.

Step 3: Now, insert data. For testing, add data into row.

Step 4: Apply SQL Server data masking

Step 5: Create a test user and test permissions to the table

How to Change or Remove a Mask?

To change the Mask definition of an existing column, run the alter column command.

Alter Table MyContacts Alter Column Credit Card Add Mask With (Function =’partial(1,XXX-XXXX-XXXX-XXX,1)’);

To remove the applied mask, use Drop Masked

Alter Table MyContacts Alter Column Credit Card Drop Masked;

Hence, Dynamic Data Masking in SQL Server allows users to protect their sensitive information according to their requirement by concealing it in table. However, you should also keep in mind that data masking is not the true encryption and it will not protect your data in all scenarios.