How to Mask Data in SQL Server

Galaxy Glossary

How do I mask sensitive data in SQL Server?

Dynamic Data Masking hides sensitive column data from non-privileged users by automatically replacing it with obfuscated values at query time.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

Why use Dynamic Data Masking in SQL Server?

Dynamic Data Masking (DDM) hides sensitive column values from non-privileged users in real time, reducing the need to duplicate data or build custom views. It is ideal for showing production data to support teams or analysts without exposing PII.

What syntax creates a masked column?

Define masking when creating the table by adding ‘MASKED WITH (FUNCTION = 'mask_function)’ to the column definition. SQL Server supports default(), email(), partial(), and random() functions.

How to add masking to an existing column?

Use ALTER TABLE … ALTER COLUMN … ADD MASKED WITH (FUNCTION = 'mask_function) to retrofit masking without rewriting the table.

How to see unmasked data?

Grant the UNMASK permission to a login or role. Users with UNMASK always see the real values; others receive the masked output.

Best practices for data masking?

Mask only columns that hold sensitive data, combine DDM with row-level security, and always test masking with different user roles to verify expected visibility.

Why How to Mask Data in SQL Server is important

How to Mask Data in SQL Server Example Usage


-- Low-privilege user should not see customer emails
CREATE USER analyst WITHOUT LOGIN;
GRANT SELECT ON Customers TO analyst;

EXECUTE AS USER = 'analyst';
SELECT id, name, email FROM Customers;
REVERT;  -- analyst sees a@XXXX.com, etc.

How to Mask Data in SQL Server Syntax


-- Create table with masked columns
CREATE TABLE Customers (
    id          INT PRIMARY KEY,
    name        NVARCHAR(100) MASKED WITH (FUNCTION = 'partial(1,&quot;XXXX&quot;,1)'),
    email       NVARCHAR(256) MASKED WITH (FUNCTION = 'email()'),
    created_at  DATETIME
);

-- Add masking to an existing column
ALTER TABLE Orders
ALTER COLUMN total_amount MONEY
    ADD MASKED WITH (FUNCTION = 'default()');

-- Grant or revoke visibility
GRANT UNMASK TO analytics_role;  -- see real data
REVOKE UNMASK FROM analytics_role;

Common Mistakes

Creating a FULLTEXT index on a masked column. DDM is incompatible with FULLTEXT; drop or move the index before masking, or mask a computed copy instead.
Assuming masking encrypts data. DDM only obfuscates query output; users with UNMASK or elevated privileges still see raw values. Combine it with encryption for true security.

Frequently Asked Questions (FAQs)

What mask functions are available?

default(), email(), random(start,end), and partial(prefix, padding, suffix) are built-in.

Does Dynamic Data Masking slow queries?

DDM is applied on the result set, so its overhead is minimal and usually unnoticeable.

Can I mask and encrypt the same column?

DDM and Always Encrypted cannot coexist on the same column. Use a separate encrypted copy or cell-level encryption if both are needed.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.