DataSunrise is sponsoring RSA Conference2024 in San Francisco, please visit us in DataSunrise's booth #6178

SQL Server Dynamic Data Masking

SQL Server Dynamic Data Masking

SQL Server Dynamic Data Masking content image


In today’s data-driven world, protecting sensitive information stored in databases is of utmost importance. Data breaches can lead to serious consequences like identity theft, financial fraud, and reputational damage. Microsoft SQL Server uses Dynamic Data Masking to hide sensitive data in real-time for protection.

This article will explain the basics of data masking. Also covered will be the features of SQL Server Dynamic Data Masking. Additionally, it will explain how it works with code examples. Lastly, it will discuss the security benefits that it offers.

What is Data Masking?

Data masking is a technique used to hide original data by replacing it with fictitious but realistic data. The goal is to protect important information and make it easy to use for tasks like software testing and user training. You can achieve this by keeping key data secure and accessible to those who need it. By balancing security and usability, users can effectively utilize the information for various purposes.

Data masking shows only the last 4 digits of a credit card number. For example, instead of displaying the full number, like 1234-XXXX-XXXX-5678, only the last 4 digits are visible. This helps to protect sensitive information from exposure.

SQL Server Dynamic Data Masking Features

SQL Server 2016 and later versions include built-in Dynamic Data Masking capabilities. Some key features are:

  • Column-level masking: You can apply masking to specific sensitive columns in a table instead of the entire database.
  • Masking formats: SQL Server has different ways to hide data, like showing only the first or last characters, randomizing, or customizing.
  • Role-based masking: SQL users and roles can set different masking rules. This allows DBAs to grant unmasked access to privileged users.
  • Ease of configuration: You can easily set up data masking using T-SQL commands without changing application code.

How Dynamic Data Masking Works

Let’s see how to configure data masking for a sample “Customers” table:


CREATE TABLE Customers (
  ID int,
      FirstName varchar(100),
  LastName varchar(100), 
  Email varchar(100),
  CreditCard varchar(20)

To mask the CreditCard column to show only last 4 digits:


ALTER TABLE Customers  

Now, when a user queries this table, the CreditCard column will display masked data:


SELECT * FROM Customers;
-- Result
ID FirstName LastName Email            CreditCard
1  John      Doe      [email protected]   XXXX-XXXX-XXXX-5678 
2  Jane      Smith    [email protected]   XXXX-XXXX-XXXX-9876

The MASKED WITH clause specifies the masking function to use. This function shows the first 0 characters, replaces 12 characters with X, and displays the last 4 characters. Some other masking functions are:

  • default() – Uses default masked value according to data type
  • random() – Generates random masked value per data type
  • email() – Shows first letter of email and masks domain with
  • Custom String – Allows specifying a custom masking format

Privileged users like sysadmin and db_owner can grant permissions to view unmasked data:



Now the TestUser will see actual unmasked credit card numbers when querying the table.

Security Benefits

SQL Server Dynamic Data Masking provides several key security advantages:

  • Helps meet compliance standards like GDPR, HIPAA, PCI-DSS by protecting sensitive data
  • Reduces risk of data breaches as only authorized users can view unmasked data
  • Allows DevOps teams to use realistic masked data for testing and troubleshooting
  • Provides a transparent extra layer of security on top of other measures like encryption
  • Easy to implement without changing application code or table structure

Important to note that data masking doesn’t replace the need for other security best practices like:

  • Principle of least privilege – Grant minimum required permissions
  • Database and backup encryption
  • Strict access controls and auditing
  • Proper patch management
  • Network security controls

Rather, data masking complements these measures as a defense-in-depth strategy.


SQL Server Dynamic Data Masking is a useful tool that hides sensitive data from unauthorized users in real-time. It supports various masking formats, role-based masking rules, and is easy to set up using T-SQL commands. Data masking protects sensitive information and reduces the impact of data breaches, helping organizations comply with privacy regulations.

SQL Server has good data masking features, but tools like DataSunrise offer more options for security, audit, and compliance. They provide extra flexibility and customization for protecting data. DataSunrise’s masking tool integrates seamlessly with SQL Server to identify and mask sensitive data using techniques like substitution, shuffling, encryption, and pseudonymization, among others.

To learn more about DataSunrise’s exceptional data management solutions, visit our team at for an online demo. Safeguard your sensitive data with the power of SQL Server Dynamic Data Masking and DataSunrise!


Data Generalization

Data Generalization

Learn More

Need Our Support Team Help?

Our experts will be glad to answer your questions.

General information:
[email protected]
Customer Service and Technical Support:
Partnership and Alliance Inquiries:
[email protected]