Classifying Data with SSMS 17.5

I upgraded SSMS to 17.5 recently and found an interesting addition. This version has incorporated the ability to classify data. With the GDPR coming for many of us, this is a welcome addition.

This is a quick look at this feature.

Classify a Database

If I select a database and right click it in SSMS, I get a few new items in the Tasks menu (as shown).

2018-02-16 13_15_58-SQLQuery1.sql - Atlas.master (sa (51))_ - Microsoft SQL Server Management Studio

I’ll select “Classify Data”, and I get a new tab opened. I see there are some recommendations and also a list of classifications of data.

2018-02-16 13_16_28-Data Classification - NBA - Microsoft SQL Server Management Studio

There’s a getting started link, which takes me to the SQL Server Security Blog. I suspect that’s an incorrect link. I think it should go here: SQL Data Discovery and Classification.

Below this, I see a list of the recommendations. This has grabbed tables that appear to continue to contain some data that might be sensitive and require classification. One of the tenets of the GDPR  is that you know your data. You aren’t allowed to figure this out later, but rather you must proactively know what data you are collecting and processing.

2018-02-16 13_17_47-Data Classification - NBA - Microsoft SQL Server Management Studio

Here we can see a few drop downs to the right. I’ll scroll and look at these. First is the Information Type. This is listed as a name, but I have other options I can set. The list is the types of that that might be sensitive information about a data subject (a human or entity) that I need to classify.

2018-02-16 13_19_36-

Beside this is the sensitivity label. My choices here are shown below. These range from public information, which removes some of my responsibility to highly confidential and applying to the GDPR.

2018-02-16 13_21_14-Data Classification - NBA - Microsoft SQL Server Management Studio

If I’m happy with these recommendations, I can select them all (or a subset) on the left. I can click the “Accept” button to add them to the classifications I have for this database.

2018-02-16 13_22_07-Data Classification - NBA - Microsoft SQL Server Management Studio

This doesn’t save them, but adds them to the list. At the top of this tab I can see the need to “Save” my changes.

2018-02-16 13_22_13-Data Classification - NBA - Microsoft SQL Server Management Studio

Once I’ve done this, I could add more, or view a report. The report shows me this:

2018-02-16 13_22_26-Data Classification - 2_16_2018 1_22 PM - PLATO_SQL2016 - Microsoft SQL Server M


My guess was that these are implemented as extended properties, which makes sense. That’s how many things could make SQL Server better, and I’m right. If I examine the EP for the firstname column in one table, I see this:

2018-02-16 13_26_46-Column Properties - firstname

This was the column I changed to public information. The lastname column in the same table is marked as confidential.

2018-02-16 13_26_55-Column Properties - lastname


Ultimately is this useful? Yes. I can see other products taking advantage of this, such as the new Data Masker from Redgate, which could let you know which columns are sensitive and not masked. I’d also expect that this is useful and important for ETL and other operations to carry this metadata to new columns that might contain transformations or movement of this data.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

4 Responses to Classifying Data with SSMS 17.5

  1. Pingback: Classifying Data In SSMS – Curated SQL

  2. Pingback: Opportunity or Restriction? | Voice of the DBA

  3. Swarnava Datta says:

    I just have one question can this be implemented on multiple DB’s on the server if all of them have the same structure ?


    • way0utwest says:

      Do you mean will the tool put the data in multiple dbs at once? No. This is a rudimentary, database by database, or really, column by column tool.

      That being said, you could have a script or job that moves extended properties to another database.


Comments are closed.