Home › SQL Server › Dynamic Data Masking – Tracking all masked columns in your database

Dynamic Data Masking – Tracking all masked columns in your database

By Samir Behara on October 23, 2017 • ( 3 )

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, Huntsville – there 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 –
https://atomic-temporary-98113587.wpcomstaging.com/2016/05/07/dynamic-data-masking-in-sql-server-2016/

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?

Resolution –

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;