Understanding Dynamic Data Masking (DDM) in SQL Server

In our earlier article, Understanding SQL Server Security Features, we explored a range of native SQL Server security mechanisms—from TDE and Always Encrypted to audit logs and data classification. In this post, we explain what Dynamic Data Masking (DDM) is, when to use it, and how to implement it effectively.

1. What is Dynamic Data Masking (DDM)?

Dynamic Data Masking (DDM) automatically masks (obscures) the values of designated columns when queried by applications or users. This lets you restrict visibility of sensitive information without modifying the stored data.

• The underlying data remains intact in the database.
• Users without proper privileges see masked values according to defined rules.
• Helps ensure developers or operators don’t access sensitive details unnecessarily.

2. Common Masking Functions

SQL Server provides several built-in masking functions:

• default(): Masks values based on the data type.
• email(): Partially masks email addresses (e.g., aXXX@XXXX.com).
• partial(prefix,”padding”,suffix): Shows part of the string and masks the rest.
• random(low,high): Returns a random value for numeric columns.

3. Implementation Steps

3.1 Create a Sample Table

CREATE DATABASE MaskingDemo;
GO
USE MaskingDemo;
GO

CREATE TABLE Customers (
    CustomerID INT IDENTITY PRIMARY KEY,
    Name  NVARCHAR(100),
    Email NVARCHAR(100) MASKED WITH (FUNCTION = 'email()'),
    Phone NVARCHAR(20)  MASKED WITH (FUNCTION = 'partial(2,"XXXXXX",2)')
);
GO

INSERT INTO Customers (Name, Email, Phone)
VALUES
('Alice', 'alice@example.com', '08012345678'),
('Bob',   'bob@example.com',   '09098765432');

3.2 Create a Masked User (SQL Authentication)

Best practice is to create the server-level login first (typically in master), then map it to a database user in MaskingDemo, and finally grant permissions.

-- 1) Create a SQL login (server scope)
USE master;
GO
CREATE LOGIN TestUser WITH PASSWORD = 'p@ssw0rd';
GO

-- 2) Create the user in MaskingDemo and map to the login
USE MaskingDemo;
GO
CREATE USER TestUser FOR LOGIN TestUser;
GO

-- 3) Grant permissions to read masked data (masked view)
GRANT SELECT ON dbo.Customers TO TestUser;
GO

Note: Ensure SQL Server is configured for SQL logins (Mixed Mode) if you plan to connect using SQL authentication.

4. Verification

When queried as an administrator (or a principal with the UNMASK permission), the actual data is returned. When logged in as TestUser, results appear masked as shown below.

Administrator:

TestUser:

5. Key Considerations

• DDM is not encryption and is not a substitute for TDE or Always Encrypted. It controls what selected users see at query time.
• Privileged principals (e.g., sysadmin) or those granted the UNMASK permission can see unmasked data.
• Certain operations (e.g., SELECT INTO, some transformations) may reveal underlying values when run by privileged users—combine DDM with proper authorization.
• For production, consider pairing DDM with Row-Level Security (RLS) and least-privilege role design.