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



Drop If Exists Feature In Sql Server 2016

If you need to delete a table , then you are used to writing code like this. If the table exists it will drop.We all have been writing code like this for years.

In SQL server you can simply drop a table like this.You can use this syntax for droping indexes,databases,roles,defaults,schemas and so on