Simple query to return columns that have been classified as sensitive in SQL Server

A few months back, in response to new regulations like the GDPR and a growing concern about data breaches and other data privacy issues, Microsoft released a new data privacy feature in SQL Server Management Studio (SSMS). Since SSMS 17.5, users have been able to use new classification functionality that sniffs out any of the columns on your database with names that sound like they contain private data and to label them with a couple of extended properties.

ClassifyData

Redgate are making moves in the data privacy space too. Last year they asked DLM Consultants to run a concierge program to test run their new stuff with real world users. I’ve been spending about 50% of my time over the last few months running that program and am quite excited about the stuff Redgate will be putting out over the next 6 to 12 months.

Part of that work is some functionality in Redgate SQL Estate Manager, currently hidden by a feature flag, that offers a (hopefully) improved classification experience¬†which integrates with the Microsoft data classifications. For example, it allows users to see all the columns that SSMS wouldn’t find be default so you can check you didn’t miss anything.

SEM-vNext screenshot

One thing I’ve seen a lot of folks do during my concierge calls is clicking the buttons in either the SSMS or SQL Estate Manager UI to set up their classifications and then trying to see how the classifications are¬†represented as extended properties by writing their own query against sys.objects. This tends to be a tedious and head-scratchy experience while someone shyly live-codes over a screen-share and gets a bit embarrassed whenever they make a typo or need to google some syntax.

With that in mind, I’ve copied a query from the SQL Estate Manager codebase (with permission) and saved it as a public GitHub gist. (Thanks Chris Lambrou for first writing it and then sharing it with me!)

If you want to query your columns and the associated privacy information saved in the extended properties, feel free to cut and paste. I also accept pull requests if you think it could be improved:

Leave a Reply

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