About a year ago I wrote about a new feature in SSMS that allows you to add a data classification to your columns. Today we’ll discuss what happens behind the scene and how to look at the classifications later.

I have been working with in health care since September and data security is a very important topic; so, it’s always good to identify any potential PHI that you may have overlooked. It’s also a good idea to tag columns so others can easily understand which columns contain PHI and which do not. This is where the data classification feature comes in  handy.

For more on the classification feature you can read my previous blog here: https://sqltechblog.com/2018/03/05/how-to-classify-your-data-for-gdpr/

So you’ve run the report and accepted the recommendations and saved. Now what? How do you view them? Run the report again? What if I just want to get details about an specific column?

Before we get there I’d like to show what happens behind the scene. When you click “SAVE” on the classification report a few queries are run for each column you’ve accepted. What queries you ask? Let’s see:

exec sp_addextendedproperty @name=N’sys_information_type_name’,@level0type=N’schema’,

@level0name=N’Person’,@level1type=N’table’,@level1name=N’Address’,

@level2type=N’column’,@level2name=N’City’,@value=N’Contact Info’

go

exec sp_addextendedproperty @name=N’sys_information_type_id’,@level0type=N’schema’,

@level0name=N’Person’,@level1type=N’table’,@level1name=N’Address’,

@level2type=N’column’,@level2name=N’City’,@value=N’5C503E21-22C6-81FA-620B-F369B8EC38D1′

go

exec sp_addextendedproperty @name=N’sys_sensitivity_label_name’,@level0type=N’schema’,

@level0name=N’Person’,@level1type=N’table’,@level1name=N’Address’,

@level2type=N’column’,@level2name=N’City’,@value=N’Confidential – GDPR’

go

exec sp_addextendedproperty @name=N’sys_sensitivity_label_id’,@level0type=N’schema’,

@level0name=N’Person’,@level1type=N’table’,@level1name=N’Address’,

@level2type=N’column’,@level2name=N’City’,@value=N’989ADC05-3F3F-0588-A635-F475B994915B’

go

That’s right. It’s adding the classification to the extended properties, which is good news because we can query for those. You’ll notice that there are 4 properties added and depending on if you use extended properties already may just add more noise. If you query SYS.EXTENDED_PROPERTIES, you’ll find it’s not overly helpful.

So how do I get the data in an easily consumable format? Try joining the data with INFORMATION_SCHEMA.COLUMNS.

Here’s a quick query that you may find useful to get the columns and their classification:

SELECT    TABLE_CATALOG as DBName,

TABLE_SCHEMA as SchemaName,

TABLE_NAME as TableName,

COLUMN_NAME as ColumnName,

ex.value as Classification,

CASE WHEN DATA_TYPE like ‘%char’ THEN CONCAT(DATA_TYPE, ‘(‘, isc.CHARACTER_MAXIMUM_LENGTH, ‘)’) ELSE DATA_TYPE END as DataType

FROM INFORMATION_SCHEMA.COLUMNS ISC

OUTER APPLY ::fn_listextendedproperty(‘sys_sensitivity_label_name’,’Schema’, isc.TABLE_SCHEMA, ‘Table’, isc.TABLE_NAME, ‘Column’, isc.COLUMN_NAME) ex

Now that we can query the classifications, you may consider using them to apply security for reports or to de-identify exported data.