# SQL Server Dynamic Data Masking

> Compare SQL Server data masking options — native Dynamic Data Masking (five mask types, SQL Server 2022 granular UNMASK) and Bytebase fleet-wide masking.

Tianzhou | 2026-05-14 | Source: https://www.bytebase.com/blog/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](https://learn.microsoft.com/en-us/sql/relational-databases/security/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

| Function | Behavior |
| --- | --- |
| `default()` | Full mask. `XXXX` for strings, `0` for numerics, `1900-01-01` for date/time, single zero byte for binary. |
| `email()` | Exposes the first letter and a constant `.com` suffix: `aXXX@XXXX.com`. |
| `random(start, end)` | Replaces numerics with a random value in the range, regenerated per query. |
| `partial(prefix, [padding], suffix)` | Custom string mask. Exposes the first _N_ and last _M_ characters; pads the middle. |
| `datetime("Y"\|"M"\|"D"\|"h"\|"m"\|"s")` | SQL Server 2022 only. Masks one component of a datetime — year, month, day, hour, minute, or second. |

```sql
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:

```sql
-- 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](https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security).

## Bytebase Dynamic Data Masking

![_](/content/blog/mysql-dynamic-data-masking/bb-masking-overview.webp)

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](https://docs.bytebase.com/security/data-masking/overview/) 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.

![_](/content/blog/mysql-dynamic-data-masking/bb-global-masking.webp)

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

![_](/content/blog/mysql-dynamic-data-masking/bb-column-masking.webp)

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.

![_](/content/blog/mysql-dynamic-data-masking/bb-grant-exemption.webp)

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.

![_](/content/blog/mysql-dynamic-data-masking/bb-sql-editor-full-masking.webp)

_Policies can also be codified via [GitOps](https://github.com/bytebase/example-database-security)._

Masking decisions land in the [audit log](/blog/bytebase-audit-logging/#what-bytebase-records). 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

|                  | SQL Server Dynamic Data Masking                  | Bytebase Dynamic Data Masking                       |
| ---------------- | ------------------------------------------------ | --------------------------------------------------- |
| Compatibility    | SQL Server 2016+, Azure SQL, MI, Synapse, Fabric | All SQL Server distributions ⭐️                    |
| Mechanism        | `MASKED WITH` on column ⭐️                      | Policy in Bytebase, applied at SQL Editor          |
| Enforced at      | Database, every read path ⭐️                    | SQL Editor                                          |
| Mask types       | Five built-in functions                          | Full, partial, MD5, range, custom                   |
| Policy mgmt      | T-SQL DDL on each database                       | Centralized UI, grants, audit log ⭐️               |
| Permission scope | DB / schema / table / column (2022+)             | Project, database, table, column                    |
| Workflow         | DDL only                                         | Request. Review. Approve. ⭐️                       |
| Row-level filter | No (pair with Row-Level Security)                | No (pair with access policy)                        |
| Price            | Free, all editions ⭐️                           | Paid                                                |

## 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](https://docs.bytebase.com/tutorials/data-masking/).