SQL Server Dynamic Data Masking

Sensitive columns — SSNs, credit cards, emails, addresses — must stay queryable for support, analytics, and development. Broad cleartext access is not the answer. Data masking is. For some workloads (GDPR, HIPAA, PCI), masking is also a legal requirement.

SQL Server ships Dynamic Data Masking in core. Every edition. Since 2016. Bytebase Dynamic Data Masking sits in front. One policy model across every SQL Server distribution and every other engine in the fleet. Request. Review. Approve. This post compares them.

SQL Server Dynamic Data Masking#

DDM is GA on SQL Server 2016 and later (every edition, including Express), Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, and SQL database in Microsoft Fabric. No plugin. No edition gate. MASKED WITH is part of CREATE TABLE and ALTER TABLE.

Masks are defined per column. The server rewrites query results for principals without UNMASK. The data on disk is unchanged.

Five mask types#

CREATE TABLE Data.Membership (
    MemberID     INT IDENTITY(1,1) PRIMARY KEY,
    FirstName    VARCHAR(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
    LastName     VARCHAR(100) NOT NULL,
    Phone        VARCHAR(12)  MASKED WITH (FUNCTION = 'default()') NULL,
    Email        VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    DiscountCode SMALLINT     MASKED WITH (FUNCTION = 'random(1, 100)') NULL,
    BirthDay     DATETIME     MASKED WITH (FUNCTION = 'datetime("Y")') NULL
);

-- Add a mask to an existing column.
ALTER TABLE Data.Membership
    ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"xxxx",0)');

-- A user without UNMASK sees masked values.
EXECUTE AS USER = 'MaskingTestUser';
SELECT FirstName, LastName, Phone, Email FROM Data.Membership;
-- Rxxxxxo   Taxxxxo   xxxx   RXXX@XXXX.com
REVERT;

Permissions#

Two permissions govern the model:

• UNMASK — return cleartext on SELECT. Grant to principals who need the underlying data.
• ALTER ANY MASK — add, change, or drop masks. Grant separately from ALTER on the table, typically to a security officer.

sysadmin, db_owner, and anyone with CONTROL on the database always see cleartext — CONTROL implicitly includes both. A DBA with db_owner is not subject to DDM.

SQL Server 2022 granular UNMASK#

Before 2022, UNMASK was database-wide — grant it once and the principal sees every masked column in the database. SQL Server 2022 (16.x) added scoped grants at four levels:

-- Column-level: only Data.Membership.FirstName.
GRANT UNMASK ON Data.Membership(FirstName) TO ServiceAttendant;

-- Table-level: every masked column on Data.Membership.
GRANT UNMASK ON Data.Membership TO ServiceLead;

-- Schema-level: every masked column under the Data schema.
GRANT UNMASK ON SCHEMA::Data TO ServiceManager;

-- Database-level: equivalent to the pre-2022 grant.
GRANT UNMASK TO ServiceHead;

Grants target Microsoft Entra (formerly Azure AD) users and groups directly. Wire DDM into a directory-driven access model without a translation layer.

What DDM does not do#

• Defend against ad-hoc inference. A principal with SELECT but not UNMASK can probe with predicates — WHERE Salary BETWEEN 99999 AND 100001 returns the masked 0 but reveals which rows match. DDM blocks accidental exposure, not malicious inference. Pair with row-level security and audit.
• Stop CONTROL holders. sysadmin, db_owner, and CONTROL on the database see cleartext. Limit who holds these.
• Gate writes. A user with masked SELECT and UPDATE can still overwrite the column. Mask authorization and write authorization are separate decisions.
• Strip the mask during SELECT INTO / INSERT INTO. Copying from a masked column without UNMASK writes masked values into the destination. Environment clones inherit the mask. ETL pipelines need UNMASK on the source.
• Preserve mask type in expressions. Any expression referencing a masked column is masked, but always with the default mask — LEFT(email, 3) returns XXX, not the email() shape.
• Apply to every column kind. Always Encrypted columns, FILESTREAM, sparse columns in a COLUMN_SET, columns referenced by computed columns, full-text index keys, and PolyBase external tables cannot be masked.
• Support indexed views. Unsupported when the base table is referenced by an indexed view.
• Return correct cross-database joins on masked columns. Remote sides ship masked values across the link. Joins and comparisons then run on masked data.
• Filter rows. DDM gates columns. For row-level control, pair with Row-Level Security.

Bytebase Dynamic Data Masking#

Native DDM has two documented gaps. CONTROL holders see cleartext. SELECT-only users infer underlying values with ranged predicates. Both follow from the same design: DDM rewrites results inside the engine, but privileges and predicates run upstream of the rewrite. Closing the gaps requires governing the query itself — not just the result.

Bytebase Dynamic Data Masking governs the query. Queries route through Bytebase's SQL Editor. Bytebase masks results before they leave the editor. db_owner and sysadmin membership on the backing instance does not bypass the policy. Ad-hoc inference becomes an access decision: granting Query rights runs through a built-in workflow — Request. Review. Approve. — every step audited.

Policies compose from three layers, evaluated in fixed precedence: Masking Exemption > Global Masking Rule > Column Masking.

1. Global Masking Rule. Workspace-level. Rules evaluate top-down. First match wins. Match conditions span environment, project, database, and data classification. Each match applies a Semantic Type, which selects a masking algorithm — full, partial, MD5, range, or custom.

2. Column Masking. Project-level override on a specific column when the global rule does not apply.

3. Masking Exemption. Named users receive time-bound Query or Export exemptions to specific databases or tables. Service accounts are not eligible. Every grant logged. Every access logged.

Masking is infectious. When a column is masked, the policy propagates to every view and derived structure that depends on it. Expressions over masked columns stay masked — without DDM's "everything collapses to default()" caveat.

Policies can also be codified via GitOps.

Masking decisions land in the audit log. Every SQL execution entry carries per-column masking metadata — masked columns, Semantic Type, matching rule — alongside user, source IP, statement, and row count. Granted exemptions, used exemptions, and policy edits are first-class audit events. The access decision and the proof of enforcement live in the same record.

Enforcement boundary: Bytebase masks queries routed through the SQL Editor. Traffic that hits SQL Server directly bypasses it — covered there by native DDM and UNMASK. The pattern is symmetric: native DDM at the database; Bytebase on the human query path, where approval and audit matter. One policy applies across SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse, Fabric SQL database, and AWS RDS for SQL Server.

Comparison#

Picking one#

• Single SQL Server instance, or single Azure SQL database. Masking must enforce regardless of client. Use native DDM. Free. In-core. The only option that masks BCP exports, SSIS, and SELECT INTO for non-UNMASK principals. On SQL Server 2022+, prefer scoped grants (GRANT UNMASK ON SCHEMA:: / ON table(column)) over the all-or-nothing 2016 model.
• Mixed fleet — SQL Server alongside Postgres, MySQL, Snowflake, RDS, or managed forks. Use Bytebase. One policy model. Every engine. Audited grants for every unmask, recorded in the same place as your access logs.
• Both. Native DDM masks at the database — direct connections, exports, ad-hoc tools. Bytebase governs the human query path through the SQL Editor, with approval and audit. They compose.

Try Bytebase Dynamic Data Masking with this tutorial.