Data Masking in SQL Server

SQL Server Logo

Data Masking.

Data Masking is the process of masking the data from users for whom you don’t wish to see the raw data in its entirety.

You control who can see the data by ensuring that they have the UNMASK database permission set, dbo user already has this permission set.



The Data Masking statement has the following functions:

Default() – this will use the default of the data type not the default constraint.

Partial() – partial gives us more control we can specify how much of a value to show and to hide the rest with different values.

Email() – masks the email so you only see the meaningful information.

Random() – with produce a random number for any of the data types (int, smallint, decimal, etc..)


When creating a table it’s worth noting that the Data Masking statement goes between the data type and the nullable statement. As the simple example shows.



If you wish to test this on some of your own tables you can execute a select statement for a user who does not have the UNMASK permission set but has SELECT permissions.  The below example shows this.



Leave a Reply

Your email address will not be published. Required fields are marked *