SQL Server Dynamic Data Masking: The Complete DBA Guide

Dynamic Data Masking (DDM) is a SQL Server security feature that hides sensitive column values from unauthorized users at query time, without changing the data stored in the database. A user without the UNMASK permission sees masked output when they SELECT a masked column. A user with UNMASK permission sees the real value. The data on disk is never altered.

DDM is not encryption. It is not a substitute for proper access control. It is a complementary layer that reduces accidental exposure of sensitive data to users who have SELECT permission on a table but should not see values like phone numbers, email addresses, credit card digits, or date of birth. Understanding exactly what DDM does and does not protect is as important as knowing how to configure it.

Masking rules are defined at the column level in a table. When a user without UNMASK permission executes a SELECT that includes a masked column, SQL Server applies the masking function to the column values in the result set before returning the data to the client. The underlying stored data is never modified. No new tables are created. No triggers fire. The masking occurs purely at the query output layer.

This means:

• Applications that write to masked columns work normally — INSERT and UPDATE operations are not affected by DDM.
• Masked data cannot be used in WHERE clauses to filter results based on the real value, but the column is still filterable using the masked representation.
• Users with CONTROL or db_owner permissions on the database always see unmasked data regardless of whether UNMASK has been explicitly granted.
• DDM applies to SELECT results. It does not affect BACKUP, index definitions, or encryption operations.

SQL Server provides five masking functions. Each applies a different transformation to the column value in the query result. The function is chosen at the time the mask is defined on a column and can be changed later without dropping and recreating the column.

Creating a table with masked columns does not require any special permission beyond the standard CREATE TABLE and ALTER on schema permissions. The mask definition is part of the column definition syntax.

Define a mask at table creation time

-- Create a table with masks defined inline on columns
CREATE TABLE dbo.CustomerContacts
(
    CustomerID   INT           PRIMARY KEY,
    FullName     NVARCHAR(100),
    Email        NVARCHAR(255) MASKED WITH (FUNCTION = 'email()'),
    Phone        VARCHAR(20)   MASKED WITH (FUNCTION = 'partial(2,"XXX-XXX-",4)'),
    CreditScore  INT           MASKED WITH (FUNCTION = 'random(300, 850)'),
    DateOfBirth  DATE          MASKED WITH (FUNCTION = 'datetime("Y")'),  -- SQL Server 2022+
    SSN          CHAR(11)      MASKED WITH (FUNCTION = 'default()')
);
GO

Add a mask to an existing column

-- Add a mask to a column in an existing table
ALTER TABLE dbo.CustomerContacts
ALTER COLUMN Email
ADD MASKED WITH (FUNCTION = 'email()');
GO

-- Add a partial mask to a phone number column
-- Shows first 3 digits and last 4 digits, masks the middle
ALTER TABLE dbo.CustomerContacts
ALTER COLUMN Phone
ADD MASKED WITH (FUNCTION = 'partial(3,"XXX",4)');
GO

-- Add a default mask to a credit card number column
ALTER TABLE dbo.PaymentData
ALTER COLUMN CardNumber
ADD MASKED WITH (FUNCTION = 'default()');
GO

Test masking behavior by impersonating a non-privileged user

-- Create a test user without UNMASK permission
CREATE USER TestViewer WITHOUT LOGIN;
GRANT SELECT ON dbo.CustomerContacts TO TestViewer;
GO

-- Insert test data
INSERT INTO dbo.CustomerContacts
    (CustomerID, FullName, Email, Phone, CreditScore, DateOfBirth, SSN)
VALUES
    (1, 'Alice Johnson', 'alice.johnson@example.com', '555-867-5309', 720, '1985-03-15', '123-45-6789');
GO

-- View as privileged user (sees real data)
SELECT CustomerID, FullName, Email, Phone, CreditScore, DateOfBirth, SSN
FROM dbo.CustomerContacts;
GO

-- View as non-privileged user (sees masked data)
EXECUTE AS USER = 'TestViewer';

SELECT CustomerID, FullName, Email, Phone, CreditScore, DateOfBirth, SSN
FROM dbo.CustomerContacts;

REVERT;
GO

-- Expected masked output for TestViewer:
-- Email:        aXXX@XXXX.com
-- Phone:        555XXXXX5309  (depending on partial parameters)
-- CreditScore:  random number between 300 and 850
-- DateOfBirth:  date with year masked per datetime("Y") function
-- SSN:          XXXX (default mask for char type)
-- FullName:     Alice Johnson (no mask defined)

Prior to SQL Server 2022, UNMASK was an all-or-nothing database-level permission. Granting UNMASK allowed a user to see unmasked data in every masked column across the entire database. From SQL Server 2022, UNMASK can be granted at four levels of granularity.

-- Grant UNMASK at column level only (SQL Server 2022+)
-- The support analyst can see the real email but not SSN or credit score
GRANT UNMASK ON OBJECT::dbo.CustomerContacts(Email) TO SupportAnalyst;
GO

-- Grant UNMASK at table level
-- The fraud team sees all unmasked columns in the PaymentData table
GRANT UNMASK ON OBJECT::dbo.PaymentData TO FraudTeamRole;
GO

-- Grant UNMASK at schema level
-- Security officers can see unmasked data across the entire Security schema
GRANT UNMASK ON SCHEMA::Security TO SecurityOfficerRole;
GO

-- Revoke granular UNMASK
REVOKE UNMASK ON OBJECT::dbo.CustomerContacts(Email) FROM SupportAnalyst;
GO

The sys.masked_columns catalog view shows all columns in the database that have a masking function applied. It extends sys.columns with two additional columns: is_masked and masking_function.

-- List all masked columns in the current database
SELECT
    SCHEMA_NAME(t.schema_id)    AS schema_name,
    t.name                      AS table_name,
    c.name                      AS column_name,
    c.masking_function,
    tp.name                     AS data_type,
    c.max_length,
    c.is_nullable
FROM sys.masked_columns         c
JOIN sys.tables                 t  ON c.object_id = t.object_id
JOIN sys.types                  tp ON c.user_type_id = tp.user_type_id
WHERE c.is_masked = 1
ORDER BY schema_name, table_name, column_name;
GO

-- Audit: users with UNMASK permission and the scope of their permission
SELECT
    dp.name                     AS principal_name,
    dp.type_desc                AS principal_type,
    perm.permission_name,
    perm.state_desc,
    perm.class_desc,
    OBJECT_NAME(perm.major_id)  AS object_name
FROM sys.database_permissions   perm
JOIN sys.database_principals    dp ON perm.grantee_principal_id = dp.principal_id
WHERE perm.permission_name = 'UNMASK'
ORDER BY dp.name;
GO

-- Change the mask function on an existing masked column
ALTER TABLE dbo.CustomerContacts
ALTER COLUMN Email
ADD MASKED WITH (FUNCTION = 'partial(1,"XXX@XXX",4)');
GO
-- Note: ADD MASKED replaces an existing mask on the column

-- Remove a mask from a column
ALTER TABLE dbo.CustomerContacts
ALTER COLUMN SSN
DROP MASKED;
GO

This is one of the most important behaviors to understand in production environments. When a user without UNMASK permission copies masked data to another table or exports it, the destination receives the masked values — not the real values.

-- A user without UNMASK running SELECT INTO
-- copies MASKED values into the new table, not real values
SELECT * INTO dbo.CustomerContacts_Backup
FROM dbo.CustomerContacts;
-- If run by a non-privileged user: all masked columns in the backup
-- contain the masked representation, not the original data.
-- The mask definition is NOT carried over to the new table.

The same behavior applies to:

• SQL Server Import and Export operations run by a non-privileged user: the exported file contains masked values
• INSERT INTO ... SELECT from a masked table: destination rows contain masked values
• Linked server queries to a masked table from a remote server: the remote server receives already-masked values and cannot unmask them

The following column types and configurations cannot have a DDM mask applied. Attempting to add a mask to these columns returns an error.

Additionally, the deprecated T-SQL statements READTEXT, UPDATETEXT, and WRITETEXT do not function correctly on columns configured with DDM for users without UNMASK permission.

These three features are complementary, not alternatives. A well-designed security architecture uses all three together for different purposes.

The technical information in this article was verified against Microsoft documentation at the time of publication. SQL Server features, cloud service capabilities, licensing terms, and configuration requirements can change between versions and cumulative updates. Always validate implementation details against current Microsoft Learn documentation before deploying to production. References in this article link directly to the authoritative Microsoft sources.