Dynamic Data Masking(DDM) in SQL Server 2016

Dynamic Data Masking(DDM)

You can use DDM in many scenarios involving sensitive data.For example in a production database there are some strict regulations from sharing the sensitive data to the developers when they are working on live data.DDM helps solve this problem because admininstrators can now give developers access to production data with all sensitive data masked from view.There is no additional development effort or coding required for this. You just define your masks.

Here I am going to create a new table with few masked columns


partial function – queries only show the first two characters and the last two characters and replaces everything in between with three dots

default function – completely hide the value

email function – only show the first character of the email address followed by a hardcoded mask  formatted to look like a email address

You can use the below query to select the masked columns

I have already added two records in the UserAccount table. I am going to select and check the mask is applied to the records

When you look at the records, can see that masking is not applied to the records.It’s because masking is applied only to the users that don’t have the UNMASK permission.the current connect have that permission that’s why nothing is masked.

I am going to create a new user for demo purpose and giving the user to select permission only

I am going to select the records as DemoUser

Now You can see all the records have mask applied.If you want to give UNMASK permission you can grant permission


You can download the source code from here



Leave a Reply

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