Dynamic Data Masking in SQL Server

Dynamic Data Masking (DDM) arrived in SQL Server 2016 and is available in every edition, including Express. It obfuscates column values in query results for users who lack a specific permission, without changing the stored data or the application queries. Support staff can look up a customer and see aXXX@XXXX.com instead of the full email; a developer with read access to production sees XXXX-XXXX-XXXX-1234 for card numbers. It is a presentation-layer control, not encryption, and it has known ways around it, but for reducing casual exposure it is cheap and effective.

I started using it after a support person pasted a screenshot with full customer emails into a chat. You need SQL Server 2016 or later, ALTER on the table and ALTER ANY MASK to add or remove masks. UNMASK is a separate database-level permission for people who should see real values.

The four functions

• default(): full masking. Strings become XXXX (or fewer X for short columns), numbers become 0, dates become 1900-01-01, binary becomes a single 0 byte.
• email(): keeps the first letter and the .com suffix: jXXX@XXXX.com. The suffix is always .com regardless of the real domain.
• partial(prefix, padding, suffix): keeps a number of leading and trailing characters and replaces the middle with a literal string: partial(0, "XXXX-XXXX-XXXX-", 4).
• random(low, high): replaces a numeric value with a random number in the range, on every read.

Masks apply to columns, not rows, and the mask is applied at the end of query evaluation, after joins and filters.

Adding masks to an existing table

ALTER TABLE dbo.Customers
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE dbo.Customers
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0, "XXX-XXX-", 4)');

ALTER TABLE dbo.Customers
ALTER COLUMN CreditLimit ADD MASKED WITH (FUNCTION = 'random(1000, 9999)');

ALTER TABLE dbo.Customers
ALTER COLUMN DateOfBirth ADD MASKED WITH (FUNCTION = 'default()');

New tables can declare the mask inline in CREATE TABLE:

CREATE TABLE dbo.Payments (
    PaymentId   INT IDENTITY PRIMARY KEY,
    CardNumber  VARCHAR(19) MASKED WITH (FUNCTION = 'partial(0, "XXXX-XXXX-XXXX-", 4)') NOT NULL,
    Amount      DECIMAL(10,2) NOT NULL
);

Adding a mask is a metadata operation and does not rewrite the table.

A user that sees masked data

CREATE USER support_reader WITHOUT LOGIN;
GRANT SELECT ON dbo.Customers TO support_reader;
GRANT SELECT ON dbo.Payments TO support_reader;

Then test with impersonation:

EXECUTE AS USER = 'support_reader';
SELECT TOP 3 CustomerId, Email, Phone, CreditLimit, DateOfBirth FROM dbo.Customers;
REVERT;

The output shows jXXX@XXXX.com, XXX-XXX-5512, a random four-digit number, and 1900-01-01.

UNMASK for the roles that need real values

UNMASK is all or nothing at the database level in 2016 and 2017; you cannot grant it per column or per table.

GRANT UNMASK TO app_service;
-- or for a role
GRANT UNMASK TO finance_role;

Members of db_owner are implicitly unmasked, as is anyone with CONTROL on the database. Application service accounts almost always need UNMASK; the point of the feature is to protect the humans who query directly.

Auditing what you've done

List every masked column in the database:

SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       OBJECT_NAME(c.object_id) AS table_name,
       c.name AS column_name,
       c.masking_function
FROM sys.masked_columns c
ORDER BY 1, 2, 3;

And confirm which principals have UNMASK:

SELECT pr.name, pe.permission_name, pe.state_desc
FROM sys.database_permissions pe
JOIN sys.database_principals pr ON pe.grantee_principal_id = pr.principal_id
WHERE pe.permission_name = 'UNMASK';

Notes and limitations

Masked data can be inferred. A user with SELECT can run SELECT CustomerId FROM dbo.Customers WHERE CreditLimit > 50000 and filter on real values, because predicates see the underlying data. Repeated range queries reveal the value. DDM is not a defence against a determined user; it prevents accidental exposure and screen-shoulder leaks. Combine it with row-level security and auditing where the data is genuinely sensitive.

SELECT INTO and INSERT ... SELECT copy masked values. A masked user who copies data into a table they own ends up with a table full of XXXX. This is the intended behaviour but surprises people running ad-hoc exports.

The ALTER COLUMN fails on certain columns. Masks cannot be applied to computed columns, FILESTREAM columns, sparse COLUMN_SET columns, or columns encrypted with Always Encrypted. Mask the source columns of a computed column instead.

Columns used in keys or indexes. You can mask a column that is part of a primary key or index; the mask only affects output. Masking a foreign-key column is allowed but usually pointless since the user can join through it anyway.

Dropping a mask:

ALTER TABLE dbo.Customers ALTER COLUMN Email DROP MASKED;

Ten minutes of setup, and support tools stop leaking full values. Just don't call it encryption when compliance asks.