Dynamic Data Masking in SQL Server for Sensitive Data Protection

Problem

Our organization needs to protect sensitive SQL Server data for specific users in our applications. How can we universally guarantee data protection whether the data is accessed in one of our business applications or an IT team member queries the data from SSMS?

Solution

Microsoft SQL Server Dynamic Data Masking (DDM) is a security feature that hides sensitive data when queried without changing the underlying data with any coding changes. Microsoft SQL Server Dynamic Data Masking includes five types of data masking functions at a column level:

Default
Email
Random
Custom String
Datetime

SQL Server Dynamic Data Masking Demonstration

Let’s start with creating a test environment:

CREATE DATABASE TestDB
GO
 
USE TestDB
GO
 
CREATE TABLE Employee
(  
   ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL,
   WorkPhoneNumber VARCHAR(50) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)'),
   ServicePeriodInYears INT MASKED WITH (FUNCTION = 'random(100, 300)'),
   BirthDate DATE MASKED WITH (FUNCTION = 'datetime("Y")') NULL,
   Salary MONEY MASKED WITH (FUNCTION = 'default()')
)
GO
 
-- Insert random data into the Employee table
INSERT INTO Employee (FirstName, LastName, Email, WorkPhoneNumber, ServicePeriodInYears, BirthDate, Salary)
VALUES
    ('John', 'Doe', 'john.doe@mytestcompany.com', '1234567', 5, '1990-05-15', 55000.00),
    ('Jane', 'Smith', 'jane.smith@mytestcompany.com', '7654321', 8, '1985-11-22', 65000.00),
    ('Michael', 'Johnson', 'michael.johnson@mytestcompany.com', '2345678', 3, '1995-07-10', 48000.00),
    ('Aram', 'Melikyan', 'aram.melikyan@mytestcompany.com', '8765432', 6, '1993-02-18', 60000.00),
    ('Sos', 'Sargsyan', 'sos.sargsyan@mytestcompany.com', '3456789', 10, '1980-08-30', 75000.00),
    ('Ann', 'Petrosyan', 'a.petrosyan@mytestcompany.com', '1234567', 5, '1990-05-15', 55000.00);
 
GO

The code above creates a test database – TestDB – and a sample table – Employee – and inserts demo data. As we can guess from the table definition, we applied masking rules for five columns. We specially chose five to illustrate all five masking functions.

So, let’s assume employees’ emails, work phone numbers, service years in the company, birthdates, and salaries should be hidden for some users. Now, let’s query the table using the same user-creator of the table:

SELECT *
FROM Employee

As we can see, nothing is hidden, and we see all values:

This is because we ran the code under the user who has no restrictions in viewing sensitive masked data.

Next, we will create a test user who has only SELECT permissions on the table and will run the same SELECT statement under that user:

--TestUser
CREATE USER TestUser WITHOUT LOGIN
 
GRANT SELECT ON Employee TO TestUser
  
EXECUTE AS USER = 'TestUser'
 
SELECT *
FROM Employee
 
REVERT -- reverts back to logged in user

Let’s explore the result:

Values are not original and have different formats

We can see that the values in the masked columns do not contain the original values and have different formats.

Let’s review all masked columns from the left to the right:

We can see that the values in the Email column have aXXX@XXXX.com format instead of containing the actual email addresses. We used the email() masking function on the Email column to hide emails this way.
Next, for the WorkPhoneNumber column, we used the Custom String masking function and partially masked it, leaving only the first number visible. We can adjust the visibility as well as the padding symbol by providing the corresponding parameters-prefix, padding, and suffix.
- MASKED WITH (FUNCTION = ‘partial(prefix,[padding],suffix)’)
For the ServicePeriodInYears column, we can see the strange, unrealistic numbers (>100) for the service period. The reason for this is that we used the random masking function for this column to generate random numbers from 100 to 300 instead of the real values.
- MASKED WITH (FUNCTION = ‘random(100, 300)’)
Looking at the BirthDate column, we notice that 2000 is shown for all employees’ birth year. This is the result of the date masking function applied to that column. It is possible to mask other date parts based on the function parameters.
- MASKED WITH (FUNCTION = ‘datetime(“Y”)’
Finally, we can see that 0.00 appears as a value in the Salary column for all employees. This is because the default masking uses 0 for numeric datatypes. It uses other default values for strings, dates, and binary types.

Suppose we want to make the Salary data visible to the accountant. In the code below, we grant the corresponding unmask permission on the Salary column to the imaginary accountant’s user-TestAccountant:

--Accountant
CREATE USER TestAccountant WITHOUT LOGIN
 
GRANT SELECT ON Employee TO TestAccountant
 
GRANT UNMASK ON Employee(Salary) TO TestAccountant
  
EXECUTE AS USER = 'TestAccountant'
 
SELECT *
FROM Employee
 
REVERT

Thus, we can see that for the accountant, employees’ salaries are visible, but the remaining masked columns are still masked as expected:

Maybe we want to make the salaries, service periods, work phone numbers, and emails visible to the managers:

--Manager
CREATE USER TestManager WITHOUT LOGIN
 
GRANT SELECT ON Employee TO TestManager
 
GRANT UNMASK ON Employee(Salary, ServicePeriodInYears, WorkPhoneNumber, Email) TO TestManager
  
EXECUTE AS USER = 'TestManager'
 
SELECT *
FROM Employee
 
REVERT

The result shows that all the columns mentioned above are visible to the TestManager user, and only the BirthDate data appears in the masked format:

Hence, we explored how dynamic data masking works in practice with these simple examples.

Conclusion

To protect sensitive data and control who can see it, we use Dynamic Data Masking (DDM) in SQL Server. DDM is a straightforward way to limit access to confidential information for users without special permissions. It doesn’t require complex coding, making it user-friendly. It keeps sensitive data hidden in query results without changing the actual database, providing a practical solution for data protection in various applications.

Next Steps

For additional information, please follow the links below:

Dynamic data masking – SQL Server | Microsoft Learn

Sergey Gigoyan

Sergey Gigoyan (LinkedIn) is a Senior Technical Architect specializing in data and databases with more than 15 years of experience. Sergey focuses on modern data architectures, database design and development, performance tuning and optimization, high availability solutions, BI development and DW design. He has worked with SQL Server, Oracle, and PostgreSQL databases, as well as cloud-based data solutions (AWS and Azure). Sergey also has extensive experience with modern data stacks such as Snowflake and dbt.

Sergey’s experience spans various industries. He had the privilege of working with IT giants such as Oracle as a Principal Data Engineer and BlackBerry as well as innovative startups. He helped deliver complex database solutions and advanced data strategies.

Sergey is also the author of “Building a Successful Career in IT – How I Did It” where he provides actionable advice on thriving in the ever-evolving IT industry.

MSSQLTips Awards: Champion (100+ tips) – 2024 | Author of the Year – 2020