Dynamic Data Masking: What a WHERE Clause Still Reveals

A masked user can still test a salary with WHERE Salary > 75000 and learn which row qualifies. Dynamic Data Masking changes the value returned; it does not stop SQL Server evaluating the real stored value.

Set Up the Masked Column

This disposable example uses two synthetic labels and salaries. The mask changes what a low-privilege query displays.

CREATE TABLE dbo.SalaryDemo
(
    EmployeeLabel varchar(20) NOT NULL PRIMARY KEY,
    Salary int MASKED WITH (FUNCTION='random(30000,90000)')
);
INSERT dbo.SalaryDemo VALUES ('UserA',72000),('UserB',81000);

CREATE USER SalaryReader WITHOUT LOGIN;
GRANT SELECT ON dbo.SalaryDemo TO SalaryReader;

The privileged query sees the stored salaries, 72,000 and 81,000. SalaryReader has SELECT only and no UNMASK permission.

Read the Masked Results

Run the same query as the low-privilege user. The displayed random values below are from one HPAI01 run and can change on another query.

Filter on the Value the User Cannot See

Now return only the label, not the salary. The WHERE clause still evaluates the underlying values and returns the row whose real salary is above 75,000.

EXECUTE AS USER = 'SalaryReader';
SELECT EmployeeLabel
FROM dbo.SalaryDemo
WHERE Salary > 75000
ORDER BY EmployeeLabel;
REVERT;

The result identifies UserB even though the caller cannot read the stored salary. Repeated comparisons can narrow the value further. Masking is a display control, not a way to stop a user testing data the query can reach.

Use Masking for Display, Not Authorization

If a user must not learn whether a row meets a condition, do not grant access to the underlying rows and rely on masking. Use permissions, a view or stored procedure designed for the required output, and row-level controls where appropriate. Test the exact queries the application can run.

Grant UNMASK only to principals that need the original values, and only at the narrowest scope available on your SQL Server version.

Frequently Asked Questions

Microsoft Docs: Dynamic Data Masking documents masking functions and UNMASK permissions.