Dynamic Data Masking (DDM) in SQL Server


Through the Dynamic Data Masking feature in SQL Server, we can hide the sensitive data by masking the data from the user who does not have permissions. (Here the data in the database is not changed).

There are 4 different functions to do that –

  • default – Entire column is masked.
  • partial – only works with string, for masking staring and / or ending characters of the column data.
  • email – shows only the first character of the column data and masks the rest.
  • random – only works with numeric, the column data is replaced by random values.

  • e.g. Create Table

  • e.g. Alter Table

  • To find the masking details applied on columns –

  • Mask permissions – 

  • Unmask and mask permission –

Granting UNMASK permission to the user allows to see the unmasked data.

  • For Azure SQL Database, we can enable and specify masking through the interface itself, select the Dynamic Data Masking option for the table, click on the Add mask button

Apply the masking format as needed.

Reference – Pluralsight – SQL Server Course 

Hope it helps..

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology !

One thought on “Dynamic Data Masking (DDM) in SQL Server”

Please share your thoughts

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.