SQL Data Discovery and Classification

SQL Server Data Discovery and classification

SQL Server Management Studio 17.5 introduced a new tool for data discovery and data classification.

This tool uses a classification engine that scans your database for columns containing sensitive data and provides a list of recommended column classifications, that you can easily review and apply the appropriate classification recommendations, as well as it also provides a facility to add manually classify columns.

Here we have taken a database Adventurework2017 that we want to classify, and using the instance of SQL Server 2019.

Right click on Database > Next, Click on Tasks, then Data Discovery and classification > Classify Data..

 

Once you click on Classify Data.., The classification engine scans your database for columns containing sensitive data and generates the Data Classification report that contains the list of recommended column classifications.

Now you can see, the number of columns with the recommendations.

 

Now to to view the list of recommended column classifications. click on click to view button.

You can see, the list of columns with classification recommendations in Grid.

 

Now you have an option to classify the Information Type by default that is recommended by classification engine but you can change it.

Therefore, you can select whether the column contains credentials, credit card, banking or any other option that is available in dropdown lists.

 

Again you also have an option to classify the Sensitivity Label, as you did earlier for InformationType, So can select any of option available in dropdown to classify the sensitivity label for column.

 

However, you notice that any column that might have not been recommended by classification engine, and that is supposed to have a senstive data then you can also manually add that column as shown below.

Click on Add Classification tab >Then at the left side, you see a window Add classification opens.

Select schema, table, column, information type, and sensitivity label from dropdown list.

click on Add button.

 

Once you click on add button, then click on Save button to complete the classification and persistently label the database columns with the new classification metadata.

 

After saving the classifications, you see the list of columns in Grid that you have added maually.

So far we have seen, how to add the column classification manually, Now add the classifications that is recommended by the classification engine.

Just check the those columns that you want to add, if you want to change the information type , and senstivity label just change it from respective dropdown lists.

Next, click on Accept selected recommendations button.

After that you see the list of classified columns, including both classified that you have added manually and that are recommended by classification engine.

To complete the final step just click on save button.

Then click on View Report button to see  the SQL Data Classification Report that displays the information of database classification state such as classified columns, tables containing sensitive data, and unique information.

You can also review all the classified that you have just added using below T-SQL query on SQL Server 2019 instances:

SELECT 
schema_name(Obj.schema_id) AS schema_name,
Obj.NAME AS table_name,
C.NAME AS column_name,
information_type,
label
FROM sys.sensitivity_classifications sc
JOIN sys.objects Obj
ON sc.major_id = Obj.object_id
JOIN sys.columns C 
ON sc.major_id = C.object_id AND sc.minor_id = C.column_id

 

For instances of SQL Server 2017 and previous, you can use below T-SQL Query.

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

Also Read..

Implementing a ROW_LEVEL security in SQL Server

Column Level Encryption/Decryption Using SYMMETRIC Keys

SQL Vulnerability Assessment

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More