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:

EDIT – 30th August 2019

Since writing this post Microsoft have added data classifications in SQL Server 2019 and Azure SQL DB as a first class object, rather than via an extended property.

Thank you to Aaron Hughs for providing this updated query in the comments below. For the record, I have not tested this code so please use it at your own risk – but it looks pretty sensible.

I’m reposting Aaron’s code as a GitHub Gist below for convenience – but all thanks should be directed to Aaron.

  4 comments for “Simple query to return columns that have been classified as sensitive in SQL Server

  1. B Clement
    October 16, 2018 at 2:42 pm

    This is good how to extract gdpr updated information, by any chance can you provide with the update statement to update those sys fields please.

    • Ademir
      October 30, 2018 at 12:37 pm

      I’m also looking for a way to Classify Columns by T-SQL or PowerShell against multiple Servers. SQL classification works fine for a Single DB, but, for multiple Servers and DB’s a script would be better.

  2. Aaron
    August 29, 2019 at 8:23 am

    the query didn’t work for me,
    please see amendment to work with azure sql database:
    SELECT
    S.name AS schema_name,
    T.name AS table_name,
    C.name AS column_name,
    TY.name AS type_name,
    IT.information_type AS information_type,
    IT.label AS sensitivity_label

    FROM sys.schemas AS S

    JOIN sys.tables AS T
    ON T.schema_id = S.schema_id

    JOIN sys.columns AS C
    ON C.object_id = T.object_id

    JOIN sys.types AS TY
    ON TY.user_type_id = C.user_type_id

    LEFT OUTER JOIN sys.sensitivity_classifications AS IT
    ON IT.major_id = C.object_id
    AND IT.minor_id = C.column_id

    ORDER BY
    S.name,
    T.name,
    C.name;

    • Alex Yates
      August 30, 2019 at 11:11 am

      Thanks for sharing Aaron. I’ve updated the post above to include your version for Azure SQL DB, with attribution.

Leave a Reply to Aaron Cancel reply

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