First it is important you have all your confidential information in your databases identified. Surely you have done this work after you took a look at Finding Sensitive Data in SQL Server. Now is the turn to obtain a sensitive data classification report with all of your findings.
Let’s begin with the easieast.
Generate report from Tasks
Have you already seen the first paragraph’s link? It’s just the same way but now you go through “Generate Report” option.
You don’t have to do anything else and the report is done. You got an easy to see graphical report with all of the configurations you did before with Data Classification.
Do you prefer plain text reports?
Almost in every place you have the option to generate the information you need through some code lines. I’ve got some from Microsoft docs.
Basically I select data from Extended Properties.
SELECT schema_name(O.schema_id) AS schema_name, O.NAME AS table_name, C.NAME AS column_name, information_type, sensitivity_label 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
You get a simple report in plain text as you can see in the image.
And if you got SQL Server 2019
Of course, as some other improvements in this version, some of them will help you to report additional information.
You can query a new catalog system view.
And we can use it this way. (Also taken from the previous official link)
SELECT schema_name(O.schema_id) AS schema_name, O.NAME AS table_name, C.NAME AS column_name, information_type, label 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
Finally you need to maintain the sensitive data classification report
You’ve got your report. Now use it and if necessary share it with your coworkers.
It is important that the DBAs team, developers and everybody who interacts with databases, know this data classification.
We cannot spread confidential information without control.
You need to update this report periodically and take a look if everything is correct. Also you must work with different areas in your organization, all the people involve in risks, security and others that can influence in data classification.