This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement data security and compliance
      --> Design and implement Dynamic Data Masking

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

What is Dynamic Data Masking?

Dynamic Data Masking (DDM) is a SQL Server and Azure SQL feature that limits the exposure of sensitive data by masking the results returned to non-privileged users without modifying the actual data stored in the database.

Unlike encryption, DDM does not change or encrypt the stored data. Instead, SQL Server dynamically replaces sensitive values with masked values when queries are executed by users who do not have permission to view the original data.

For example, the database may contain:

A privileged user sees:

A non-privileged user may see:

The underlying data never changes.

Why Use Dynamic Data Masking?

Organizations frequently store sensitive information such as:

• Personally Identifiable Information (PII)
• Social Security Numbers
• Credit card numbers
• Email addresses
• Phone numbers
• Employee salaries
• Medical information

Not every user who queries the database should have unrestricted access to these values.

DDM allows developers to:

• Reduce accidental data exposure
• Protect sensitive fields
• Simplify application development
• Support compliance initiatives
• Allow customer support personnel to work with realistic-looking data

How Dynamic Data Masking Works

When a user executes a query:

1. SQL Server checks whether the user has permission to view unmasked data.
2. If the user has the UNMASK permission, actual values are returned.
3. Otherwise, SQL Server substitutes masked values before sending the results.

The database itself remains unchanged.

Dynamic Data Masking Architecture

Database
│
├── Actual Data
│      987-65-4321
│
├── User A
│      Has UNMASK permission
│
│      Result:
│      987-65-4321
│
└── User B
       No UNMASK permission
       Result:
       XXX-XX-4321

Benefits of Dynamic Data Masking

DDM provides several important advantages.

Easy to Implement

Masking is configured using T-SQL without requiring application changes.

No Data Duplication

The original data remains stored only once.

Transparent to Applications

Applications continue issuing the same queries.

No application code changes are required.

Supports Least Privilege

Users receive only the information they need.

Helps Meet Compliance Requirements

Although DDM is not encryption, it helps organizations reduce unnecessary exposure of sensitive information.

Dynamic Data Masking vs Encryption

Microsoft expects candidates to understand that DDM is not a replacement for encryption technologies such as Always Encrypted or Transparent Data Encryption (TDE).

Supported Masking Functions

SQL Server supports several built-in masking functions.

Default Mask

Masks data according to its data type.

Example:

Original:

John Smith

Masked:

XXXX

Syntax:

MASKED WITH (FUNCTION = 'default()')

Email Mask

Designed specifically for email addresses.

Original:

john.smith@email.com

Masked:

jXXX@XXXX.com

Syntax:

MASKED WITH (FUNCTION = 'email()')

Partial Mask

Reveals part of a string while masking the remainder.

Example:

Original:

555-123-4567

Masked:

XXX-XXX-4567

Syntax:

MASKED WITH
(
FUNCTION='partial(prefix,padding,suffix)'
)

Example:

MASKED WITH
(
FUNCTION='partial(0,"XXX-XXX-",4)'
)

Random Mask

Returns a random value within a specified numeric range.

Example:

Original Salary

85000

Masked

43782

Syntax

MASKED WITH
(
FUNCTION='random(1,100000)'
)

Useful when exact values should never be exposed.

Creating a Masked Column

Example:

CREATE TABLE Customers
(
    CustomerID INT,
    Name NVARCHAR(100),
    Email NVARCHAR(200)
    MASKED WITH (FUNCTION='email()'),
    SSN CHAR(11)
    MASKED WITH
    (
        FUNCTION='partial(0,"XXX-XX-",4)'
    )
);

Adding a Mask to an Existing Column

ALTER TABLE Customers
ALTER COLUMN Email
ADD MASKED
WITH (FUNCTION='email()');

Removing a Mask

ALTER TABLE Customers
ALTER COLUMN Email
DROP MASKED;

Granting UNMASK Permission

Privileged users may view actual values.

GRANT UNMASK TO HRManager;

Revoking Permission

REVOKE UNMASK FROM HRManager;

Viewing Mask Definitions

View masking metadata.

SELECT *
FROM sys.masked_columns;

Useful during administration and auditing.

DDM with Azure SQL Database

Dynamic Data Masking is fully supported in:

• Azure SQL Database
• Azure SQL Managed Instance
• SQL Server

Azure SQL also provides portal-based configuration through the Azure Portal.

Developers can create masks without writing T-SQL.

Limitations of Dynamic Data Masking

Candidates should understand these limitations.

It Is Not Encryption

Anyone with sufficient permissions can retrieve actual values.

Database Administrators Can View Data

Members of powerful administrative roles can bypass masking.

Cannot Stop Inference Attacks

Users may infer values through repeated queries.

Not Intended for High-Security Scenarios

Highly confidential data should use:

• Always Encrypted
• Transparent Data Encryption
• Row-Level Security
• Proper access control

Expressions Return Masked Values

If a masked column is used in expressions, the expression also returns masked results for users without UNMASK permission.

Best Practices

Mask Only Sensitive Columns

Avoid unnecessary masking.

Combine with Other Security Features

Use together with:

• Always Encrypted
• Row-Level Security
• Transparent Data Encryption
• Microsoft Entra authentication
• Least privilege access

Grant UNMASK Sparingly

Only trusted users should receive this permission.

Test Using Non-Privileged Accounts

Always verify what ordinary users actually see.

Audit Sensitive Access

Monitor who receives UNMASK permissions.

Dynamic Data Masking vs Row-Level Security

DP-800 Exam Tips

Candidates should be able to:

• Explain what Dynamic Data Masking is.
• Differentiate masking from encryption.
• Identify supported masking functions.
• Create masked columns using CREATE TABLE and ALTER TABLE.
• Grant and revoke the UNMASK permission.
• Understand when DDM is appropriate.
• Recognize DDM limitations.
• Choose DDM versus Always Encrypted, TDE, or Row-Level Security based on the security requirement.
• Understand that DDM protects against accidental exposure, not malicious users with elevated privileges.

Practice Exam Questions

Question 1

A company wants customer support representatives to view only partially masked Social Security numbers while allowing HR staff to view the full values.

Which SQL Server feature best meets this requirement?

A. Transparent Data Encryption

B. Dynamic Data Masking

C. Always Encrypted

D. Data Compression

Answer: B

Explanation: Dynamic Data Masking displays masked values to unauthorized users while allowing authorized users with the appropriate permissions to see the original data.

Question 2

Which statement about Dynamic Data Masking is true?

A. It encrypts data stored on disk.

B. It permanently changes stored values.

C. It masks query results for users without UNMASK permission.

D. It replaces encryption.

Answer: C

Explanation: Dynamic Data Masking only alters the data presented in query results. The stored values remain unchanged.

Question 3

Which masking function is specifically designed for email addresses?

A. partial()

B. random()

C. default()

D. email()

Answer: D

Explanation: The email() masking function preserves the general format of an email address while obscuring most of the information.

Question 4

Which statement best describes the partial() masking function?

A. It encrypts selected characters.

B. It returns random values.

C. It permanently replaces data.

D. It reveals specified prefix and suffix characters while masking the middle.

Answer: D

Explanation: The partial() function exposes configurable leading and trailing characters while masking the remaining portion of the value.

Question 5

Which permission allows a user to view unmasked data?

A. SELECT

B. CONTROL

C. UNMASK

D. VIEW DEFINITION

Answer: C

Explanation: Users granted the UNMASK permission can view the original values instead of the masked representations.

Question 6

Which system catalog view displays information about masked columns?

A. sys.columns

B. sys.masked_columns

C. sys.tables

D. sys.database_permissions

Answer: B

Explanation: The sys.masked_columns catalog view contains metadata about all columns configured with Dynamic Data Masking.

Question 7

A database administrator wants to protect highly confidential financial information from administrators who manage the database server.

Which technology should be preferred over Dynamic Data Masking?

A. Always Encrypted

B. Dynamic Data Masking

C. Partial masking

D. Random masking

Answer: A

Explanation: Always Encrypted ensures that sensitive data remains encrypted even from database administrators because encryption and decryption occur on the client side.

Question 8

Which statement about Dynamic Data Masking and application code is generally correct?

A. Applications must always be rewritten.

B. DDM requires client-side decryption.

C. Existing queries usually continue to work without modification.

D. Applications cannot access masked tables.

Answer: C

Explanation: Dynamic Data Masking is transparent to most applications, allowing existing queries to function normally while returning masked data when appropriate.

Question 9

A developer executes the following statement:

GRANT UNMASK TO SalesManager;

What is the effect?

A. The SalesManager can modify masked columns.

B. The SalesManager can bypass row-level security.

C. The SalesManager can view original values in masked columns, provided they also have permission to access the data.

D. All users inherit the UNMASK permission.

Answer: C

Explanation: The UNMASK permission allows a user to see unmasked values but does not grant access to data that the user is otherwise unauthorized to read.

Question 10

Which security strategy provides the strongest protection for sensitive database columns?

A. Use only Dynamic Data Masking.

B. Use only Row-Level Security.

C. Use only Transparent Data Encryption.

D. Combine Dynamic Data Masking with encryption, least-privilege access, and other SQL Server security features.

Answer: D

Explanation: Dynamic Data Masking is most effective as part of a layered security strategy that also includes encryption, access controls, auditing, and other SQL Server security features.

Go to the DP-800 Exam Prep Hub main page