Dynamic Data Masking: Masking the sensitive data from users

Dynamic data masking (DDM) limits sensitive data exposure by masking it to non-privileged users. – MS docs

Why DDM is even required: lets assume you have a table Employee with column phone number and have to provide read permission to developers but still want to hide the phone numbers from them. Previously we used to either use column level encryption or remove the data from the table altogether or create a view on top of the table without having this column and provide read permission to developers on this View. But starting from SQL Server 2016 Microsoft has introduced Dynamic Data Masking, which greatly reduces the effort in handling such issues.

How it works: We have a table Employee with below data.

Let us say that we want to mask the PhoneNumber which is currently visible to everyone having read permission onto the table. To add the DDM for this column use below command.

ALTER TABLE [dbo].[Employee] 
ALTER COLUMN PhoneNumber varchar(100) MASKED WITH (FUNCTION = 'default()');  

Once masked, when we run select on the table as user ‘Developer101’ whom we haven’t provided the UNMASK permission.

EXECUTE AS USER = 'Developer101'
SELECT * FROM [dbo].[Employee]
REVERT

The “PhoneNumber” is now not visible to the user User1. To let the user see the unmask data use below command.

GRANT UNMASK TO Developer101;  

To remove DDM from the table use below command.

ALTER TABLE [dbo].[Employee]   
ALTER COLUMN [PhoneNumber] DROP MASKED;  

Microsoft provides multiple functions to mask the data which are as below:

https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017#defining-a-dynamic-data-mask

The functions which are used the most are Default and Partial.

I hope it helps!