Dynamic Data Masking (DDM) is one of those SQL features (available in SQL Server, Azure SQL DB, Azure SQL MI, SQL Database in Microsoft Fabric) that both developers and DBAs can rally behind. Why? Because it delivers a simple, built-in way to protect sensitive data—like phone numbers, emails, or IDs—without rewriting application logic or duplicating security rules across layers. With just a single line of T-SQL, you can configure masking directly at the column level, ensuring that non-privileged users see only obfuscated values while privileged users retain full access. This not only streamlines development but also supports compliance with data privacy regulations like GDPR and HIPAA, etc. by minimizing exposure to personally identifiable information (PII).
In this first post of our DDM series, we’ll walk through a real-world scenario using the default masking function to show how easy it is to implement and how much development effort it can save.
Scenario: Hiding customer phone numbers from support queries
Imagine you have a support application where agents can look up customer profiles. They need to know if a phone number exists for the customer but shouldn’t see the actual digits for privacy. In a traditional approach, a developer might implement custom logic in the app (or a SQL view) to replace phone numbers with placeholders like “XXXX” for non-privileged users. This adds complexity and duplicate logic across the app.
With DDM’s default masking, the database can handle this automatically. By applying a mask to the phone number column, any query by a non-privileged user will return a generic masked value (e.g. “XXXX”) instead of the real number. The support agent gets the information they need (that a number is on file) without revealing the actual phone number, and the developer writes zero masking code in the app.
This not only simplifies the application codebase but also ensures consistent data protection across all query access paths. As Microsoft’s documentation puts it, DDM lets you control how much sensitive data to reveal “with minimal effect on the application layer” – exactly what our scenario achieves.
Using the ‘Default’ Mask in T-SQL :
The ‘Default’ masking function is the simplest mask: it fully replaces the actual value with a fixed default based on data type. For text data, that default is XXXX. Let’s apply this to our phone number example. The following T-SQL snippet works in Azure SQL Database, Azure SQL MI and SQL Server:
SQL
-- Step 1: Create the table with a default mask on the Phone column
CREATE TABLE SupportCustomers (
CustomerID INT PRIMARY KEY,
Name NVARCHAR(100),
Phone NVARCHAR(15) MASKED WITH (FUNCTION = 'default()') -- Apply default masking
);
GO
-- Step 2: Insert sample data
INSERT INTO SupportCustomers (CustomerID, Name, Phone)
VALUES (1, 'Alice Johnson', '222-555-1234');
GO
-- Step 3: Create a non-privileged user (no login for simplicity)
CREATE USER SupportAgent WITHOUT LOGIN;
GO
-- Step 4: Grant SELECT permission on the table to the user
GRANT SELECT ON SupportCustomers TO SupportAgent;
GO
-- Step 5: Execute a SELECT as the non-privileged user
EXECUTE AS USER = 'SupportAgent';
SELECT Name, Phone FROM SupportCustomers WHERE CustomerID = 1
Alternatively, you can use Azure Portal to configure masking as shown in the following screenshot:
Expected result: The query above would return Alice’s name and a masked phone number. Instead of seeing 222-555-1234, the Phone column would show XXXX. Alice’s actual number remains safely stored in the database, but it’s dynamically obscured for the support agent’s query.
Meanwhile, privileged users such as administrator or db_owner which has CONTROL permission on the database or user with proper UNMASK permission would see the real phone number when running the same query.
How this helps Developers :
By pushing the masking logic down to the database, developers and DBAs avoid writing repetitive masking code in every app or report that touches this data. In our scenario, without DDM you might implement a check in the application like:
- If user_role == “Support”, then show “XXXX” for phone number, else show full phone.
With DDM, such conditional code isn’t needed – the database takes care of it. This means:
- Less application code to write and maintain for masking
- Consistent masking everywhere (whether data is accessed via app, report, or ad-hoc query).
- Quick changes to masking rules in one place if requirements change, without hunting through application code.
From a security standpoint, DDM reduces the risk of accidental data exposure and helps in compliance scenarios where personal data must be protected in lower environments or by certain roles, while reducing the developer effort drastically.
In the next posts of this series, we’ll explore other masking functions (like Email, Partial, and Random etc) with different scenarios. By the end, you’ll see how each built-in mask can be applied to make data security and compliance more developer-friendly!
Reference Links :
Microsoft