The default, email, random and partial masks, the permissions that add or bypass them, and why masking alone doesn't stop inference.
From Ultra Transcenders DP-700 by Tony Rough (coming December 2026)
Dynamic data masking (DDM) hides sensitive values in query results without changing the stored data. It’s useful for preventing accidental exposure, not for stopping a determined user with query access.
| Function | Result | Example definition |
|---|---|---|
default() |
Full mask by type: XXXX for strings, 0 for numbers, 1900-01-01 00:00:00.0000000 for dates and times, a single zero byte for binary |
Phone varchar(12) MASKED WITH (FUNCTION = 'default()') |
email() |
First letter plus XXX@XXXX.com, for example aXXX@XXXX.com |
Email varchar(100) MASKED WITH (FUNCTION = 'email()') |
random(start, end) |
A random number in the range, for numeric types | ALTER COLUMN Score ADD MASKED WITH (FUNCTION = 'random(1, 12)') |
partial(prefix, "padding", suffix) |
Exposes the first and last characters with custom padding | ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') |
ALTER TABLE dbo.Employee ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
GRANT UNMASK ON dbo.Employee TO [hr_team];
REVOKE UNMASK ON dbo.Employee TO [hr_team];
ALTER TABLE dbo.Employee ALTER COLUMN Email DROP MASKED;The statements add a mask to an existing column, let a role see real values, take that right away again, and finally remove the mask. Masks can be added in CREATE TABLE or with ALTER TABLE ... ALTER COLUMN, including on SQL analytics endpoint tables.
| Operation | Permission needed |
|---|---|
| Create a table with masked columns | CREATE TABLE and ALTER on the schema |
| Add, replace or remove a mask | ALTER ANY MASK and ALTER on the table |
| See masked data | SELECT on the table |
| See unmasked data | UNMASK on the column (or table), or CONTROL on the database |
Because Admin, Member and Contributor hold CONTROL, they always see unmasked data; only Viewers and shared users without UNMASK see the masks.
Common trap: Relying on masking to stop a user working out salaries - a user who can query the table can filter with ranges such as
WHERE Salary > 99999 AND Salary < 100001and infer the real values; combine DDM with object-level, column-level and row-level security.
This note is one section of Ultra Transcenders DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.
Due on Amazon in December 2026, in Kindle and paperback editions.
About the book · DP-700 terms in the glossary · All DP-700 study notes
How purpose, skills and coding level decide between Dataflow Gen2, a pipeline and a notebook, and where Copy job and Apache Airflow jobs fit.
How authoring style, output, storage, state and latency decide between eventstreams, Spark structured streaming and eventhouses.
When a KQL database should ingest data, query it through a standard OneLake shortcut, or accelerate the shortcut, and what each costs.
How the five eventstream window types group events in time, how they overlap and how to write them in the SQL operator.
When to reload everything or only changes, and which change-detection method catches inserts, updates and deletes.
How starter, custom, capacity and custom live pools differ in node sizes, start-up time, sizing against the capacity and job admission.
How skills, data location and transformation type decide between Dataflow Gen2, Spark notebooks, KQL update policies and warehouse T-SQL.