Does Dynamic Data Masking encrypt data?

No. It changes query results for users without the required permission. The stored data remains unchanged, so encryption and access controls are separate requirements.

Who can see unmasked values?

Principals with appropriate privileges, including UNMASK combined with SELECT, can see the underlying values. Administrators and highly privileged roles need separate control and monitoring.

Can DDM stop a malicious user inferring data?

Not by itself. Microsoft warns that users may infer values through carefully constructed queries. Use least privilege, auditing and other security controls for sensitive data.

Can masking be applied to existing columns?

Yes, supported columns can be altered to add a masking function. Test dependencies, applications and the chosen function before production rollout.

What masking functions are available?

Microsoft documents default, email, random and partial masking functions, with platform and data-type rules. Select a function that preserves only what the use case needs.

Can UNMASK be granted at a granular level?

SQL Server 2022 introduced more granular UNMASK permissions at database, schema, table and column levels. Combine them with SELECT and verify effective permissions.

SQL Server Dynamic Data Masking: Uses, Limits and Setup

Article Summary:

Use Dynamic Data Masking to reduce casual exposure while understanding its permissions, limitations and place in a wider security design.

Dynamic Data Masking, or DDM, can reduce casual exposure of sensitive values in query results. It is useful when a support or reporting user needs access to rows but not the full email address, account reference or personal identifier. It is not encryption and should not be treated as a boundary against a determined or highly privileged user.

What DDM actually does

Microsoft's Dynamic Data Masking documentation explains that masking is applied to query results for users who do not have permission to see unmasked data. The underlying value is not changed. Applications with the right permissions continue to receive the original value.

Default masks for several data types
Email-style masking
Partial character exposure
Random masking for supported numeric types

Start with the threat and use case

A good use case is reducing unnecessary exposure in production support, demonstrations or broad reporting access. Define who needs the full value, who only needs a recognisable shape and which tasks should use a sanitised non-production copy instead.

DDM is weaker when a user can issue arbitrary queries against sensitive rows. Microsoft notes that inference may be possible. Treat least privilege, auditing, encryption and data minimisation as separate controls.

Implement with explicit permissions

Inventory existing SELECT, CONTROL, db_owner and sysadmin rights before adding masks. A mask that privileged users bypass gives little practical reduction. From SQL Server 2022, granular UNMASK permission can be granted at database, schema, table or column scope, but the user also needs SELECT.

Use groups instead of individual grants
Keep administrative roles small
Audit changes to masks and UNMASK
Test effective permissions, not intended permissions

Test the application path

Some applications validate formats, sort on displayed values or export query results. A mask may change those behaviours for lower-privileged users. Test normal screens, reports, exports, search, joins and error handling with the real execution identity.

Avoid using production values in development simply because they are masked through one query route. A controlled sanitisation process is safer for copied data.

Operate DDM as part of data protection

Maintain a register of masked columns, their owners and the approved unmasking roles. Include masks in schema deployment and drift checks. Review the design when a new integration, report or support role is introduced.

Classify the data first
Apply least privilege
Mask only for a defined purpose
Monitor privileged access
Review alongside encryption and audit controls

Talk to a database specialist

A change to support, licensing, cloud architecture or a production database should be based on evidence from your own estate. Pro DBA can assess the current position, identify the material risks and help you plan the next step. Contact the Pro DBA team to discuss your environment.