SQL Server Management Studio – Data Classification

Hello Friends,

The first step in Data Security or Data Protection is identifying sensitive data. We have to check every table one by one and identify every column which stores sensitive data, so that we can categorize identified sensitive columns into further sub categories. This data classification is itself a time consuming process. To simplify this process, Microsoft introduced a new tool in SQL Server Management Studio i.e. Data Discovery and Classifications. This tool will help us in four steps – Discovering, Classifying, Labeling and Reporting. In this bog post, you will learn about this new SQL Server tool.

To use this tool, Right Click on Database in SQL Server Management Studio then -> Tasks -> Classify Data….

SSMS - Data Classification 1

If you will click on classify data then you will get below output as mention below:

SSMS - Data Classification 2

If you will click on recommendations then you will get the details as shown below. You will get the details like Schema name, table name, Column name and most important other two columns – Information type and Sensitivity Label.

SSMS - Data Classification 3

Here Information Type is having some predefined categorization as mention below:

SSMS - Data Classification 4

There are some predefined Sensitivity Labels as mention below:


SSMS - Data Classification 5

From the above classification recommendations, you can select as per your company/security policy. After making selection, click on “Accept Selected Recommendations”. This will add selected columns under classified column list.

SSMS - Data Classification 7

You can add more columns from recommendations to Classified Column List. There may be various other columns which should be part of classified column list and are not listed under recommendations. For such columns, you can add those using Add Classification.

SSMS - Data Classification 8

You can save this Data Classification. This will add sensitivity detail about data using extended properties and you can run report later by clicking on “View Report”.

SSMS - Data Classification 9

SSMS - Data Classification 10

Using this Report, you can view all the details about Data Classification for database.


Prince Kumar Rastogi

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook


About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.