Sensitive Data Classification Report in SQL Server

ES: Reporte de Información confidencial en SQL Server

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.

• sys_information_type_name
• sys_sensitivity_label_name

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.

• sys.sensitivity_classifications

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.