Database classifications have changed


Some time back I wrote about the new data classification features in Azure and SQL Server Management Studio. If you’ve done quite a bit of work classifying your data using the extended properties, guess what?

That’s right. It’s changed in SQL Server 2019 and for the better! Older versions of SQL Server stored classification meta data in Extended Properties which is not ideal. Starting with SQL Server 2019 a new system table was added: sys.sensitivity_classifications.

There are two main reasons why this is much better than the old way. First, it’s much easier to query. Second, it not has its own permissions.

Securing the data classifications can be done by locking down the “VIEW ANY SENSITIVITY CLASSIFICATION” permission. You can grant this permission to any data dictionary tools you may have easily as well.

Adding classifications can be done via SSMS or by using “ADD SENSITIVITY CLASSIFICATION”.

Viewing existing classifications can also be done with SSMS or with the following query.

SELECT AS schema_name, AS table_name, AS column_name,
     Label AS sensitivity_label_name,
     Label_ID AS sensitivity_label_id,
     Information_Type AS information_type_name,
     Information_Type_ID AS information_type_id

     JOIN sys.tables t ON sys.sensitivity_classifications.major_id = t.object_id  AND t.temporal_type <> 1
     JOIN sys.schemas s ON t.schema_id = s.schema_id
     JOIN sys.columns c ON sys.sensitivity_classifications.major_id = c.object_id
         AND sys.sensitivity_classifications.minor_id = c.column_id

Enjoy and happy classifying!

Original post (opens in new tab)