New features in SQL 2016: Dynamic Data Masking

As a running up to the new release of SQL 2016 I would like to share some research I did about the new features that will be available. Today I want to talk about Dynamic Data Masking. It is a nifty, small but quite interesting new feature.

Books Online describes it as this:
Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

So what does it not do? It’s not an encryption method. It really does what it says.


So masking. What does that mean?

It’s easier to give an example. After saving a credit card into your paypal account, you will never see your full credit card number again on their website.

It will always be shown like XXXX XXXX XXXX 1234, although they know the full number. That is data masking. This method is now also available in SQL 2016. In this short article I would like to describe how you can configure this.



First of all, what are the benefits?

If you are working with sensitive data, like credit card numbers or social security numbers. It’s never a good idea to show them in your application. Since you never know who is watching your screen. A second benefit is that the data that is send over the network is worthless. If a hacker catches the data, he sees a lot of XXXXXXX and a small part of the real data. So the benefits all have to do with Security.
Microsoft has foreseen 4 different masking types:

  1. Default
    Everything will be masked depending on your data type Characters will be converted to X’s. Numbers will be converted to 0’s.
    Note: Masking will only be 4 characters long.
  2. Email
    First character of the email address will be readable together with the suffix (.com, .co.uk, …)
  3. Custom
    This type has 3 parameters.
    i. How many prefix characters need to be readable?
    ii. How many suffix characters need to be readable?
    iii. What do you want to show in between?
  4. Random
    Converts any numeric data type into a number in a certain range.


Following example shows the use of all of them. Our data, a table that contains a list of all the employees in a certain company. It includes the account number where their wages are paid on, and there social security number for insurance reason.

FirstName NVARCHAR(100),
LastName NVARCHAR(100),
Email NVARCHAR(100),
BankAccountNumber BIGINT,
SocialSecurityNumber NVARCHAR(20)

INSERT INTO Employee(FirstName, LastName, Email, BankAccountNumber, SocialSecurityNumber)
(‘John’, ‘Doe’,’john.doe@domain.com’, 3219876512, ‘123-456-789’),
(‘Jane’, ‘Doe’,’jane.doe@domain.com’, 9876543210, ‘321-654-987’),
(‘Lewis’, ‘Doe’,’lewis.doe@domain.com’, 3754796548, ‘789-123-456’)

SELECT * FROM Employee


This is what we were used to. Now let’s implement the data masking:

FirstName NVARCHAR(100) MASKED WITH (FUNCTION = ‘partial(1, “……”, 1)’),
LastName NVARCHAR(100),
Email NVARCHAR(100) MASKED WITH (FUNCTION = ’email()’),
BankAccountNumber BIGINT MASKED WITH (FUNCTION = ‘random(1000000000,9999999999)’),
SocialSecurityNumber NVARCHAR(20) MASKED WITH (FUNCTION = ‘default()’)

INSERT INTO Employee(FirstName, LastName, Email, BankAccountNumber, SocialSecurityNumber)
(‘John’, ‘Doe’,’john.doe@domain.com’, 3219876512, ‘123-456-789’),
(‘Jane’, ‘Doe’,’jane.doe@domain.com’, 9876543210, ‘321-654-987’),
(‘Lewis’, ‘Doe’,’lewis.doe@domain.com’, 3754796548, ‘789-123-456’)

SELECT * FROM Employee


As you noticed the result of our query still returns all the data. Since you created the table, you are the owner and you can see everything. We need a user that only has SELECT permissions on that table.

GRANT SELECT ON Employee TO PublicUser;

EXECUTE AS USER = ‘PublicUser’;
SELECT * FROM Employee;

And now we see that the data is masked.


FirstName is replaced by the first letter of the name, the last letter of the name and in between some dots. Email is replaced by the first letter, the suffix and all the rest became X’s. BankAccountNumber has been replaced with a random number in the range we gave and the Social SecurityNumber is fully replaced by XXXX.

If you want to give a user the privilege to unmask data, then you need to grant him UNMASK permissions.


EXECUTE AS USER = ‘PublicUser’;
SELECT * FROM Employee;


Revoking can be done by this:


Microsoft has foreseen a new system table (sys.masked_columns) to get data about the masked columns.

SELECT t.name, mc.name, mc.masking_function
FROM sys.tables t
JOIN sys.masked_columns mc
ON t.object_id = mc.object_id
WHERE t.name = ‘Employee’


Don’t forget that this does not change anything to how to update or insert data in those columns. You just enter the correct data; SQL will mask it. It’s also good to know that if you do a SELECT INTO or INSERT INTO, the data will be copied masked if you don’t have the UNMASK permissions.

I hope you found this short blogpost interesting. Next time I will write about another new feature namely ‘Row-Level-Security’.

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...