How to export Data Classification

PROBLEM

Export Data ClassificationIn my previous post I described how Data Discovery and Classification is a half done tool.

Personally I loved it the first time saw it. But the more you use it the more you see that is very basic. At the same time I understand that Microsoft cannot keep providing software for free. So if you really need to do a proper Data Discovery and Classification you have to knock at the door of big vendors like Red-Gate or ApexSQL.

The tool is limited to what it can offer out-of-the-box which is:

  • Classify data: The idea of data discovery based on the column name is good but I needed more modular options that’s why I had to tune the query.
  • Generate Report: Is the report that you are going to show to your manager
  • Set Information Protection Policy File: You can add fields in the classification or search words through a JSON file (InformationProtectionPolicy).
  • Export Information Protection Policy File: You can export it (…you see, Microsoft, we need to export this 🙂 )
  • Reset Information Protection Policy to Default: Reset

In my case I have already classified 50 columns and I now need to export the same classification to 6 databases.

There is no way to do that.

SOLUTION

I mean, there was no way to do that till now.

I have categorized a bunch of columns on AdventureWorks in SQL Server 2019 and if I run this query the last column will print the exportation query for you:

SELECT 
    schema_name(O.schema_id) AS schema_name,
    O.NAME AS table_name,
    C.NAME AS column_name,
	label,
	information_type,
	rank,
	rank_desc
	,'ADD SENSITIVITY CLASSIFICATION TO ' 
	+ schema_name(O.schema_id) + '.' + O.NAME + '.' + C.NAME + 
	' WITH ( LABEL=''' 
	+ CAST(label AS NVARCHAR(50)) + 
	''', INFORMATION_TYPE='''
	+ CAST(information_type AS NVARCHAR(50)) +
	''', RANK='
	+ rank_desc +
	' )'
FROM sys.sensitivity_classifications sc
    JOIN sys.objects O
    ON  sc.major_id = O.object_id
	JOIN sys.columns C 
    ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id

Export Data Classification 2019

And because I’m awesome you are lucky (the project I’m working on is using SQL Server 2016 😐 ) I also have a query for SQL Server 2017 and earlier; hold your breath, this is going to be much longer: if you notice we need 4 queries in order to classify each field

SELECT schema_name(O.schema_id) AS schema_name
	,O.NAME AS table_name
	,C.NAME AS column_name
	,information_type
	,sensitivity_label
	,'exec sp_addextendedproperty @name=N''sys_information_type_name'',@level0type=N''schema'',@level0name=N''' 
	+ schema_name(O.schema_id) + 
	''' ,@level1type=N''table'',@level1name=N''' 
	+ O.NAME + 
	''' ,@level2type=N''column'',@level2name=N''' 
	+ C.NAME + 
	''' ,@value=N''' 
	+ CAST(information_type AS NVARCHAR(50)) + 
	''';'
FROM (
	SELECT IT.major_id
		,IT.minor_id
		,IT.information_type
		,L.sensitivity_label
	FROM (
		SELECT major_id
			,minor_id
			,value AS information_type
		FROM sys.extended_properties
		WHERE NAME = 'sys_information_type_name'
		) IT
	FULL OUTER JOIN (
		SELECT major_id
			,minor_id
			,value AS sensitivity_label
		FROM sys.extended_properties
		WHERE NAME = 'sys_sensitivity_label_name'
		) L ON IT.major_id = L.major_id
		AND IT.minor_id = L.minor_id
	) EP
JOIN sys.objects O ON EP.major_id = O.object_id
JOIN sys.columns C ON EP.major_id = C.object_id
	AND EP.minor_id = C.column_id


UNION ALL -- Second query UNION ALL

SELECT schema_name(O.schema_id) AS schema_name
	,O.NAME AS table_name
	,C.NAME AS column_name
	,information_type
	,sensitivity_label
	,  'exec sp_addextendedproperty @name=N''sys_information_type_id'',@level0type=N''schema'',@level0name=N'''
  +
  schema_name(O.schema_id)
  +
  ''' ,@level1type=N''table'',@level1name=N'''
  +
  O.NAME
  +
  ''' ,@level2type=N''column'',@level2name=N'''
  +
  C.NAME
  +
  ''' ,@value=''' + CAST(NEWID() AS varchar(100)) + ''';'
FROM (
	SELECT IT.major_id
		,IT.minor_id
		,IT.information_type
		,L.sensitivity_label
	FROM (
		SELECT major_id
			,minor_id
			,value AS information_type
		FROM sys.extended_properties
		WHERE NAME = 'sys_information_type_name'
		) IT
	FULL OUTER JOIN (
		SELECT major_id
			,minor_id
			,value AS sensitivity_label
		FROM sys.extended_properties
		WHERE NAME = 'sys_sensitivity_label_name'
		) L ON IT.major_id = L.major_id
		AND IT.minor_id = L.minor_id
	) EP
JOIN sys.objects O ON EP.major_id = O.object_id
JOIN sys.columns C ON EP.major_id = C.object_id
	AND EP.minor_id = C.column_id

UNION ALL -- Third query UNION ALL

SELECT schema_name(O.schema_id) AS schema_name
	,O.NAME AS table_name
	,C.NAME AS column_name
	,information_type
	,sensitivity_label
	,'exec sp_addextendedproperty @name=N''sys_sensitivity_label_name'',@level0type=N''schema'',@level0name=N'''
  +
  schema_name(O.schema_id)
  +
  ''' ,@level1type=N''table'',@level1name=N'''
  +
  O.NAME
  +
  ''' ,@level2type=N''column'',@level2name=N'''
  +
  C.NAME
  +
	''' ,@value=N''' 
	+ CAST(sensitivity_label AS NVARCHAR(50)) + 
	''';' 
FROM (
	SELECT IT.major_id
		,IT.minor_id
		,IT.information_type
		,L.sensitivity_label
	FROM (
		SELECT major_id
			,minor_id
			,value AS information_type
		FROM sys.extended_properties
		WHERE NAME = 'sys_information_type_name'
		) IT
	FULL OUTER JOIN (
		SELECT major_id
			,minor_id
			,value AS sensitivity_label
		FROM sys.extended_properties
		WHERE NAME = 'sys_sensitivity_label_name'
		) L ON IT.major_id = L.major_id
		AND IT.minor_id = L.minor_id
	) EP
JOIN sys.objects O ON EP.major_id = O.object_id
JOIN sys.columns C ON EP.major_id = C.object_id
	AND EP.minor_id = C.column_id

UNION ALL -- Forth query UNION ALL

SELECT schema_name(O.schema_id) AS schema_name
	,O.NAME AS table_name
	,C.NAME AS column_name
	,information_type
	,sensitivity_label
	,  'exec sp_addextendedproperty @name=N''sys_sensitivity_label_id'',@level0type=N''schema'',@level0name=N'''
  +
  schema_name(O.schema_id)
  +
  ''' ,@level1type=N''table'',@level1name=N'''
  +
  O.NAME
  +
  ''' ,@level2type=N''column'',@level2name=N'''
  +
  C.NAME
  +
  ''' ,@value=''' + CAST(NEWID() AS varchar(100)) + ''';'
FROM (
	SELECT IT.major_id
		,IT.minor_id
		,IT.information_type
		,L.sensitivity_label
	FROM (
		SELECT major_id
			,minor_id
			,value AS information_type
		FROM sys.extended_properties
		WHERE NAME = 'sys_information_type_name'
		) IT
	FULL OUTER JOIN (
		SELECT major_id
			,minor_id
			,value AS sensitivity_label
		FROM sys.extended_properties
		WHERE NAME = 'sys_sensitivity_label_name'
		) L ON IT.major_id = L.major_id
		AND IT.minor_id = L.minor_id
	) EP
JOIN sys.objects O ON EP.major_id = O.object_id
JOIN sys.columns C ON EP.major_id = C.object_id
	AND EP.minor_id = C.column_id
	order by 1,2,3

CONCLUSION

Is not over, next post will be about how to export the actual data masking.

Related

Leave a Reply

You have to agree to the comment policy.