What Dynamic Data Masking Does Not Tell Your Auditor

"The national ID column is masked" is a sentence often heard in audit meetings, and it usually means SQL Server's Dynamic Data Masking (DDM). DDM is a useful feature. But the auditor's question is not "do asterisks appear on screen", it is "whose hands did the sensitive data reach, and how do you know". This article uses sample queries to show what DDM protects, where it falls short, and which control is needed when data leaves production.

How Dynamic Data Masking Works

DDM arrived with SQL Server 2016. A mask function is defined on a column; the data on disk does not change, only the query result comes back masked to a user without permission. The built in functions are default(), email(), random() and partial(); SQL Server 2022 added datetime() for dates.

ALTER TABLE dbo.Customer
  ALTER COLUMN NationalId ADD MASKED WITH (FUNCTION = 'partial(0,"*******",4)');

-- A user without UNMASK permission:
SELECT CustomerId, NationalId FROM dbo.Customer WHERE CustomerId = 42;
-- 42 | *******4521

Limit 1: Predicates Run on Real Values

The mask applies only to the result; the WHERE clause runs on the real value. That means a user who can write queries can find a value by guessing. Microsoft's own documentation states plainly that DDM is not designed to prevent this kind of inference.

-- Masked in the result, but the predicate sees the real value:
SELECT COUNT(*) FROM dbo.Customer
WHERE CustomerId = 42 AND NationalId LIKE '1%';   -- 1 or 0

-- Repeating this digit by digit reveals the full value.
SELECT CustomerId FROM dbo.Employee WHERE Salary > 150000;  -- masked column, real filter

So DDM works well in an application that shows a fixed screen to a user who cannot write queries; against a user who can write ad hoc queries it is a convenience, not a security boundary.

Limit 2: Privileged Users See It Unmasked

The UNMASK permission removes the mask. Anyone with CONTROL on the database, that is members of db_owner and system administrators, holds it implicitly. The people with the most access to production data, the database administrators, never see the mask at all. Before SQL Server 2022 UNMASK was granted database wide; from 2022 it can be granted at schema, table and column level:

GRANT UNMASK ON dbo.Customer(NationalId) TO SupportAnalyst;   -- SQL Server 2022+

One more detail: if a user reading the masked column copies the data to another table with SELECT INTO or INSERT INTO ... SELECT, the masked values are what land in the new table. That is good behaviour, but a copy made by a privileged user is unmasked, and DDM does not know where that copy went.

Limit 3: It Keeps No Record

DDM is a display rule, not a recording mechanism. It does not keep who saw the unmasked value, which query they ran or where they saved the result. For those questions you must also record SELECT statements on sensitive tables with SQL Server Audit. On its own DDM cannot answer the auditor's question "who saw this column unmasked in the last three months".

When Data Leaves: Masking at Delivery

Production data leaves most often as a query result: for an analyst, a support team or an audit exercise. At that point the question changes: who asked, on what grounds, and which columns went to whom in the clear? What is needed here is masking applied inside the delivered file and every decision going on the record.

In SQL Change Guard, production data leaves through a data request and goes through these steps:

• Two detectors. The column name catalogue catches a column whose name is sensitive (for example NationalId, Iban) without looking at the data. Content patterns look at the values themselves in a sample taken from the result; checksum algorithms run for national identifiers, tax numbers, card numbers and IBANs, so a random number is not treated as sensitive by mistake.
• Time out means masked. If the time budget of content detection runs out, the columns not yet examined are masked rather than left open.
• An unmasked column needs a reason. If a column is asked for in the clear, a reason is written and, in the installation default, an extra approval step opens.
• Every column's decision is recorded. A masked column is written with the rule that masked it, an unmasked column with its reason (no sensitive value in the sample, no data, and so on). If the decision planned when the request was saved differs from the one at delivery, the evidence file shows it as an exception.
• The file travels encrypted, downloads are recorded. The result is not shown on screen; it is delivered as a password protected encrypted package. Who downloaded the file and for what stated reason is written down, and the file is deleted at the end of its retention period.

Let us state the limit too: these steps apply only to data requests opened through the product. If a user connects directly with SQL Server Management Studio and runs a query, the product neither sees nor masks it; on that path DDM, SQL Server Audit and narrowing direct access are what apply.

Which One, When

Frequently Asked Questions

Does DDM encrypt the data?

No. The data stays in the clear on disk, in backups and in the transaction log. Encryption needs separate features such as Always Encrypted or Transparent Data Encryption, each aimed at a different threat.

Do PostgreSQL and Oracle have DDM?

PostgreSQL does not have it in core; extensions such as postgresql_anonymizer provide dynamic masking. In Oracle the same job is done by Data Redaction, which is part of the separately licensed Advanced Security option.

What happens with the product's masking if the target column already has DDM?

If the SQL account the product uses on the target has no UNMASK permission, the result reaches the product already masked by DDM, and the product's masking is applied on top. If it has the permission, the values arrive in the clear and the masking decision at delivery is made by the product's own rules.

How do I show masking decisions to an auditor?

For every data request opened through the product, the evidence file holds each column's decision and reason. For a period view, the "Query Result Activity" report lists delivered results and the masking decision, and the "Data Access and Export Log" report lists who downloaded the file and for what stated reason.