Dynamic Data Masking in SQL Server

In the dynamic data masking there are four types they are,

1. Default mask

2. Email mask

3. Random mask and

4. Partial mask

In this example, I have demonstrated using all these four types of masks.

Step 1: Create a new table and insert data into the table. In my example, I have created employee Table and inserted new values into EmpID, FirstName, LastName, Salary, Email and Phone number columns.

Step 2: Next, create a new user and grant select login permission for that particular user for the employee table. In my example, the TestUser can view all the records because the query is not been masked yet.

Step 3: In this step, I used default masking for the LastName column on the employee table. The syntax is “Alter table [Table Name] ALTER [Column Name] ADD MASKED WITH (FUNCTION = ‘default()’)”. Once the masking is applied, we can execute as the Testuser and check that the LastName column is completely masked.

Step 4: In this step, I used Email masking for the Email column on the employee table. The syntax is “Alter table [Table Name] ALTER [Column Name] ADD MASKED WITH (FUNCTION = ‘email()’)”. Once the masking is applied, we can execute as the Testuser and see that the email column is masked and exposes only first letter of an email address with the character @ and the suffix .com.

Step 5: In this step, I used Random masking for the salary column on the employee table. The syntax is “Alter table [Table Name] ALTER [Column Name] ADD MASKED WITH (FUNCTION = ‘random([start range], [end range])’)”. Once the masking is applied, we can execute as the Testuser and see that the salary column is masked with random numbers from 1 to 9.

Step 6: Here, I used partial masking for the Phone number column on the employee table. The syntax is “Alter table [Table Name] ALTER [Column Name] ADD MASKED WITH (FUNCTION = ‘partial (prefix, [padding], suffix)’)”. Once the masking is applied, we can execute as the Testuser and see that the phone column is masked according to the given custom padding as 3 prefix and 0 in suffix.

Further reading

If you found this article helpful, you might want to check out these related resources,

• Outsourcing Database Administration: Everything you must know
• How to import and export bulk data using Bulk Copy Program