Configure Dynamic Data Masking on the email column in:
SalesLT.Customer
The column is normally:
EmailAddress
Use the built-in masking function:
email()
Microsoft documents that Dynamic Data Masking hides sensitive data in query results for nonprivileged users without changing the stored data. The built-in email() masking function exposes the first letter and returns the masked format aXXX@XXXX.com, which exactly matches the requirement.
Method 1 — SSMS / T-SQL Method
This is the fastest and most reliable method.
Step 1: Connect to db1
Open SQL Server Management Studio.
Connect to the Azure SQL logical server that hosts db1.
Open a query window against database:
db1
Step 2: Apply the email mask
Run:
ALTER TABLE [SalesLT].[Customer]
ALTER COLUMN [EmailAddress]
ADD MASKED WITH (FUNCTION = ' email() ' );
This adds a Dynamic Data Masking rule to the EmailAddress column. The actual email address remains stored in the table, but users without permission to view unmasked data will see the masked value. Microsoft’s documented syntax for adding an email mask is ALTER COLUMN Email ADD MASKED WITH (FUNCTION = ' email() ' ).
Step 3: Verify that the column is masked
Run:
SELECT
OBJECT_SCHEMA_NAME(mc.object_id) AS schema_name,
OBJECT_NAME(mc.object_id) AS table_name,
c.name AS column_name,
mc.masking_function
FROM sys.masked_columns AS mc
JOIN sys.columns AS c
ON mc.object_id = c.object_id
AND mc.column_id = c.column_id
WHERE OBJECT_SCHEMA_NAME(mc.object_id) = ' SalesLT '
AND OBJECT_NAME(mc.object_id) = ' Customer '
AND c.name = ' EmailAddress ' ;
Expected result:
schema_name SalesLT
table_name Customer
column_name EmailAddress
masking_function email()
Step 4: Test as a non-administrative user
If you have a test user, run:
EXECUTE AS USER = ' TestUser ' ;
SELECT TOP (10)
EmailAddress
FROM SalesLT.Customer;
REVERT;
Expected output should look like:
aXXX@XXXX.com
bXXX@XXXX.com
cXXX@XXXX.com
A user with administrative privileges, db_owner, or UNMASK permission can still see the original email value. Microsoft states that users with administrative rights such as server admin, Microsoft Entra admin, and db_owner can view the original unmasked data.
Method 2 — Azure Portal Method
Use this if the simulation expects portal configuration.
Step 1: Open db1
Sign in to the Azure portal.
Search for SQL databases.
Open database:
db1
Step 2: Open Dynamic Data Masking
From the db1 page:
Security > Dynamic Data Masking
Microsoft states that for Azure SQL Database, Dynamic Data Masking can be configured in the Azure portal from the SQL database configuration pane under Security > Dynamic Data Masking.
Step 3: Add a masking rule
Add a mask for the email column:
Setting
Value
Schema
SalesLT
Table
Customer
Column
EmailAddress
Masking field format
Email
Masking function
email()
Then select:
Save
The portal may show the mask type simply as:
Email
That is the correct option because it maps to the email() masking function.
Important Permission Check
Dynamic Data Masking only affects users who do not have permission to view unmasked data.
If a non-administrative user was previously granted UNMASK, remove it:
REVOKE UNMASK TO [UserName];
Or, if a role was granted UNMASK, revoke it from the role:
REVOKE UNMASK TO [RoleName];
Do not grant UNMASK to normal users. UNMASK allows users to bypass masking and see the original values. Microsoft documents that UNMASK permission controls whether users can view masked or original data.
Final Exam-Lab Action
Run this against db1:
ALTER TABLE [SalesLT].[Customer]
ALTER COLUMN [EmailAddress]
ADD MASKED WITH (FUNCTION = ' email() ' );
Then verify:
SELECT
OBJECT_SCHEMA_NAME(object_id) AS schema_name,
OBJECT_NAME(object_id) AS table_name,
name AS column_name,
masking_function
FROM sys.masked_columns
WHERE OBJECT_SCHEMA_NAME(object_id) = ' SalesLT '
AND OBJECT_NAME(object_id) = ' Customer '
AND name = ' EmailAddress ' ;
The task is complete when non-administrative users querying SalesLT.Customer.EmailAddress see masked email values such as:
aXXX@XXXX.com
