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
s.name AS schema_name,
t.name AS table_name,
c.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_idFROM
sys.sensitivity_classifications
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!