Dynamic Data Masking – Tracking all masked columns in your database

I have presented on Making Developers lives easier with SQL Server 2016 multiple times over the last couple of months, and I must admit that even though the content is the same – every presentation is different depending upon the attendees and their expertise levels. During the Gulf Coast Code Camp, Mobile and DevSpace Conference, Huntsvillethere were some interesting questions I was asked on Dynamic Data Masking during/after my presentation. I thought of writing a quick blog post to answer those questions. Honestly this is something which I plan to start doing more often – since every time I give a presentation there are few questions which stand out and and some times I have to come back and do more research to find the right answer. Well it is never too late to make a start!

Dynamic Data Masking in SQL Server 2016 is a new built-in security feature which limits access to sensitive information stored in the database for unauthorized users. Data is masked on the fly and the underlying data in the database does not change.

It is very simple to implement and does not require any coding changes either in the application side or database side. One thing to remember here is that it is not a replacement for end to end encryption.

You can read about Dynamic Data Masking in details here –

Dynamic Data Masking Working

Problem Statement

Coming back to the question which was asked on Dynamic Data Masking –

Once you have implemented Dynamic Data Masking across multiple tables in your database to protect your sensitive data, how do you track all the masked columns in one place?


To view all the column/table details where masking functions have been used in your database, you can leverage a new system view named as ‘masked_columns‘ and write a simple T-SQL query to extract the information —

— View the columns where the Masking function is applied
SELECT c.name 'ColumnName', tbl.name 'TableName',
c.is_masked 'IsMasked', c.masking_function 'MaskingFunction'
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl
ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;

The query returns the list of all columns where the masking function has been used in the database –

Dynamic Data Masking - Masking Function


Categories: SQL Server, SQL Server 2016

Tags: ,

3 replies

  1. Very nice collections of cool fonts. Nice work.


  2. Dynamic data masking is working only in same query window with specific user without login once we ran in other new query tab not working


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: