Exploring the SQL Server Management Studio Data Classification Feature

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

Pre-requisites

In the new SQL Server Management Studio version 17.5, released 15th Feb 2018

There is a new feature called Data Discovery and Classification feature described here:-

https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-data-discovery-and-classification

Research

So what can we glean from a quick Bingle?

Brent Ozar ran it on a case sensitive server and it fails with an invalid object name ‘information_schema.columns’ error, I’ve certainly seen (caused) this error and the underlying reason is that the correct case for INFORMATION_SCHEMA is uppercase.

https://www.brentozar.com/archive/2018/02/new-sql-server-management-studio-17-5-classified/

Thomas La Rock got further with his experiments and provides a nice summary of the main features of the product and explores what happens when you have column names in another language.

https://thomaslarock.com/2018/02/sql-data-discovery-and-classification/

Introduction

OK, let’s start by doing a very quick summary of the functionality and then dig a bit deeper to investigate how the proposed classifications are produced and the strengths and weaknesses.

So to bring up the suggested classifications for a database we simply right click on the database name and then pick Tasks>Classify Data…

We then get a suggestion of the columns that might contain sensitive data.

We can use the drop downs on each row to adjust the information types and sensitivity labels for each table and column in turn and fill in the check boxes in the left-hand margin and then when we are ready we can “Accept selected recommendations” and click the save button to persist the data into the database.

One potential gotcha here is if you have in memory tables you will get an error and it will look like the save has failed entirely but actually details are saved for all other tables and columns.

Limitations

Actually the adventureworks database is a good one for testing this feature – for example there is a table HumanResources.JobCandidate which contains an XML column called resume – the initial suggestions do not refer to this column at all but if you browse it in the grid view and then click the xml link you will quickly discover that no attempt has been made to parse the XML but that it is full to the brim with sensitive information, names, addresses, telephone number and former employments.

Manual Remediation

OK let’s try and manually remedy that.

First click the Add Classification button

Secondly when the dialog appears on the right-hand side (purple box added for highlighting purposes) choose the schema, table and column using the drop downs and specify the information type and sensitivity label.

Thirdly the save button is now enabled – so click here to persist your changes.

If we view the report – we can see that this entry is now added

As I mentioned earlier other people have also provided an overview of the functionality and reports, so I won’t elaborate further on the interface.

Exploring the Internals

Instead let’s take a look under the covers – I simply relied on the tried and tested investigative step of running a SQL Server trace (using the replay template) and we can quickly gain a LOT more information of how this feature is built.

The first thing that happens is that SQL runs a query to retrieve any existing data classification metadata.

SELECT
s.name AS schema_name,
t.name AS table_name,
c.name AS column_name,
EP1.value AS information_type_name,
EP2.value AS information_type_id,
EP3.value AS sensitivity_label_name,
EP4.value AS sensitivity_label_id
FROM sys.columns c
LEFT JOIN sys.tables t ON
t.object_id = c.object_id
LEFT JOIN sys.schemas s ON
s.schema_id = t.schema_id
LEFT JOIN sys.extended_properties EP1 ON
c.object_id = EP1.major_id and
c.column_id = EP1.minor_id and
EP1.name = 'sys_information_type_name'
LEFT JOIN sys.extended_properties EP2 ON
c.object_id = EP2.major_id and
c.column_id = EP2.minor_id and
EP2.name = 'sys_information_type_id'
LEFT JOIN sys.extended_properties EP3 ON
c.object_id = EP3.major_id and
c.column_id = EP3.minor_id and
EP3.name = 'sys_sensitivity_label_name'
LEFT JOIN sys.extended_properties EP4 ON
c.object_id = EP4.major_id and
c.column_id = EP4.minor_id and
EP4.name = 'sys_sensitivity_label_id'
WHERE
(
EP1.value IS NOT NULL OR
EP2.value IS NOT NULL OR
EP3.value IS NOT NULL OR
EP4.value IS NOT NULL
)

Extended Properties

Here we can quite clearly see that the metadata is stored using extended properties.

If we reverse engineer this information and we know the rudiments of extended properties we can quickly work out how to add our own entries programmatically via the back end, in this case for the column rate in HumanResources.EmployeePayHistory which we might choose to classify as Financial and Confidential. So using the script below and executing it

--'sys_information_type_name'
--'sys_information_type_id'
--'sys_sensitivity_label_name'
--'sys_sensitivity_label_id'

--Financial
--C44193E1-0E58-4B2A-9001-F7D6E7BC1373
--Confidential
--331F0B13-76B5-2F1B-A77B-DEF5A73C73C2

--HumanResources
--EmployeePayHistory
--Rate

EXEC sp_addextendedproperty
@name = N'sys_information_type_name',
@value = 'Financial',
@level0type = N'Schema', @level0name = 'HumanResources',
@level1type = N'Table',  @level1name = 'EmployeePayHistory',
@level2type = N'Column', @level2name = 'Rate';



EXEC sp_addextendedproperty
@name = N'sys_information_type_id',
@value = 'C44193E1-0E58-4B2A-9001-F7D6E7BC1373',
@level0type = N'Schema', @level0name = 'HumanResources',
@level1type = N'Table',  @level1name = 'EmployeePayHistory',
@level2type = N'Column', @level2name = 'Rate';



EXEC sp_addextendedproperty
@name = N'sys_sensitivity_label_name',
@value = 'Confidential',
@level0type = N'Schema', @level0name = 'HumanResources',
@level1type = N'Table',  @level1name = 'EmployeePayHistory',
@level2type = N'Column', @level2name = 'Rate';



EXEC sp_addextendedproperty
@name = N'sys_sensitivity_label_id',
@value = '331F0B13-76B5-2F1B-A77B-DEF5A73C73C2',
@level0type = N'Schema', @level0name = 'HumanResources',
@level1type = N'Table',  @level1name = 'EmployeePayHistory',
@level2type = N'Column', @level2name = 'Rate';

When we run the front-end report it correctly picks up the information we just added via the calls to sp_addextendedproperty see the extra row in our report, highlighted below.

Once SQL has checked for metadata it goes about collecting the column name information and here it appears to use a loop over each table in turn and for each table uses sp_executesql to get back a list of column names.

exec sp_executesql N'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_SCHEMA=@schema AND TABLE_NAME=@table)',N'@schema nvarchar(14),@table nvarchar(8)',@schema=N'HumanResources',@table=N'Employee'

The final step is to compare the column names with the predefined dictionary table using the SQL in appendix 1. Here we can see entries using wildcards (%) for several languages – English, German, Spanish, Portuguese, French. But of concern to me who lives and works in Stockholm no Swedish.

Summary

So we can see that this feature is very much an early beta and has several shortcomings

  1. Cannot work on case sensitive databases
  2. Cannot parse XML columns
  3. Cannot add extended properties to in memory tables (tries and fails)
  4. Works from a fixed and limited dictionary
  5. Cannot parse content to recognise telephone or credit card numbers for example

None the less with a bit of knowledge of the internals and some simple scripting we can quickly extend this functionality and it does provide a common standard way to tag databases for GDPR / data protection purposes and this is a good start.

For the next post we will explore using back-end scripts to parse Swedish language columns and extend support to new languages.
[/code]

Appendix 1

DECLARE @Dictionary TABLE

(
pattern     NVARCHAR(128),
info_type      NVARCHAR(128),
sensitivity_label    NVARCHAR(128),
can_be_numeric    BIT
)

INSERT INTO @Dictionary (pattern, info_type, sensitivity_label, can_be_numeric)
VALUES
('%username%'    ,'Credentials'      , 'Confidential'     ,1),
('%pwd%'         ,'Credentials'      , 'Confidential'     ,1),
('%password%'    ,'Credentials'      , 'Confidential'     ,1),
('%email%'       ,'Contact Info'     , 'Confidential - GDPR'    ,0),
('%e-mail%'      ,'Contact Info'     , 'Confidential - GDPR'    ,0),
('%last%name%'   ,'Name'             , 'Confidential - GDPR'    ,0),
('%first%name%'  ,'Name'             , 'Confidential - GDPR'    ,0),
('%surname%'     ,'Name'             , 'Confidential - GDPR'    ,0),
('%mainden%name%','Name'             , 'Confidential - GDPR'    ,0),
('%addr%'        ,'Contact Info'     , 'Confidential - GDPR'    ,0),
('%phone%'       ,'Contact Info'     , 'Confidential - GDPR'    ,1),
('%mobile%'      ,'Contact Info'     , 'Confidential - GDPR'    ,1),
('%area%code%'   ,'Contact Info'     , 'Confidential - GDPR'    ,1),
('%reset%code%'  ,'Credentials'      , 'Confidential'     ,1),
('%birthday%'    ,'Date Of Birth'    , 'Confidential - GDPR'    ,1),
('%date%of%birth%','Date Of Birth'   , 'Confidential - GDPR'    ,1),
('dob'           ,'Date Of Birth'    , 'Confidential - GDPR'    ,1),
('ssn'           ,'SSN'              , 'Confidential - GDPR'    ,1),
('%ss_num%'      ,'SSN'              , 'Confidential - GDPR'    ,1),
('%ssnum%'       ,'SSN'              , 'Confidential - GDPR'    ,1),
('sin'           ,'SSN'              , 'Confidential - GDPR'    ,1),
('%employeessn%' ,'SSN'              , 'Confidential - GDPR'    ,1),
('%passport%'    ,'National ID'      , 'Confidential - GDPR'    ,1),
('%Pasaporte%'   ,'National ID'      , 'Confidential - GDPR'    ,1),
('%social%security%'      ,'SSN'     , 'Confidential - GDPR'    ,1),
('%soc%sec%'        ,'SSN'        , 'Confidential - GDPR'    ,1),
('%security%'          ,'Other'      , 'Confidential'     ,1),
('ssid'          ,'SSN'        , 'Confidential - GDPR'    ,1),
('%tax%id%'         ,'National ID'      , 'Confidential - GDPR'    ,1),
('itin'          ,'National ID'      , 'Confidential - GDPR'    ,1),
('%driver%'         ,'National ID'      , 'Confidential - GDPR'    ,1),
('%pass%'           ,'Credentials'      , 'Confidential'     ,1),
('%personal%'          ,'Other'      , 'Confidential'     ,1),
('%identification%'       ,'National ID'      , 'Confidential - GDPR'    ,1),
('%Identificación%Fiscal%','National ID'      , 'Confidential - GDPR'    ,1),
('%street%'         ,'Contact Info'     , 'Confidential - GDPR'    ,0),
('%city%'           ,'Contact Info'     , 'Confidential - GDPR'    ,0),
('%postal%'         ,'Contact Info'     , 'Confidential - GDPR'    ,1),
('%zip%'         ,'Contact Info'     , 'Confidential - GDPR'    ,1),
('%identification%'       ,'National ID'      , 'Confidential - GDPR'    ,1),
('%id%number%'         ,'National ID'      , 'Confidential - GDPR'    ,1),
('%national%id%'       ,'National ID'      , 'Confidential - GDPR'    ,1),
('%credit%'         ,'Credit Card'      , 'Confidential'     ,1),
('%card%'           ,'Credit Card'      , 'Confidential'     ,1),
('%account%'        ,'Credentials'      , 'Confidential'     ,1),
('%tax%'         ,'Financial'     , 'Confidential'     ,1),
('%paypal%'         ,'Financial'     , 'Confidential'     ,1),
('%payment%'        ,'Financial'     , 'Confidential'     ,1),
('%banking%'        ,'Banking'       , 'Confidential'     ,1),
('%routing%no%'        ,'Banking'       , 'Confidential'     ,1),
('%savings%acc%'       ,'Banking'       , 'Confidential'     ,1),
('%debit%acc%'         ,'Banking'       , 'Confidential'     ,1),
('%insurance%'         ,'Financial'     , 'Confidential'     ,1),
('%ccn%'         ,'Credit Card'      , 'Confidential'     ,1),
('%debit%'          ,'Credit Card'      , 'Confidential'     ,1),
('%visa%'           ,'Credit Card'      , 'Confidential'     ,1),
('%mastercard%'        ,'Credit Card'      , 'Confidential'     ,1),
('%pmt%'         ,'Financial'     , 'Confidential'     ,1),
('%cvv%'         ,'Credit Card'      , 'Confidential'     ,1),
('%amount%'         ,'Financial'     , 'Confidential'     ,1),
('%amt%'         ,'Financial'     , 'Confidential'     ,1),
('%compensation%'         ,'Financial'     , 'Confidential'     ,1),
('%currency%'          ,'Financial'     , 'Confidential'     ,1),
('iban'          ,'Banking'       , 'Confidential'     ,1),
('%iban%code%'         ,'Banking'       , 'Confidential'     ,0),
('%iban%num%'          ,'Banking'       , 'Confidential'     ,0),
('%routing%number%'       ,'Banking'       , 'Confidential'     ,1),
('%patient%'        ,'Health'     , 'Confidential - GDPR'    ,1),
('aba'           ,'Banking'       , 'Confidential'     ,1),
('%aba%routing%'       ,'Banking'       , 'Confidential'     ,1),
('%bank%routing%'         ,'Banking'       , 'Confidential'     ,1),
('%swift%code%'        ,'Banking'       , 'Confidential'     ,1),
('%swift%routing%'        ,'Banking'       , 'Confidential'     ,1),
('%swift%num%'         ,'Banking'       , 'Confidential'     ,1),
('%bic%code%'          ,'Banking'       , 'Confidential'     ,1),
('%bic%num%'        ,'Banking'       , 'Confidential'     ,1),
('%expy%'           ,'Credit Card'      , 'Confidential'     ,1),
('%expm%'           ,'Credit Card'      , 'Confidential'     ,1),
('%invoice%'        ,'Financial'     , 'Confidential'     ,1),
('%clinic%'         ,'Health'     , 'Confidential - GDPR'    ,1),
('%medical%'        ,'Health'     , 'Confidential - GDPR'    ,1),
('%treatment%'         ,'Health'     , 'Confidential - GDPR'    ,1),
('%healthcondition%'      ,'Health'     , 'Confidential - GDPR'    ,1),
('%atmkaart%'          ,'Credit Card'      , 'Confidential'     ,1),
('%medication%'        ,'Health'     , 'Confidential - GDPR'    ,1),
('%health%'         ,'Health'     , 'Confidential - GDPR'    ,1),
('%prescription%'         ,'Health'     , 'Confidential - GDPR'    ,1),
('ip'            ,'Networking'    , 'Confidential'     ,0),
('%[^h]ip%address%'       ,'Networking'    , 'Confidential'     ,0),
('ip%address%'         ,'Networking'    , 'Confidential'     ,0),
('%mac%address%'       ,'Networking'    , 'Confidential'     ,0),
('%acct%nbr%'          ,'Banking'       , 'Confidential'     ,1),
('%acct%num%'          ,'Banking'       , 'Confidential'     ,1),
('%acct%no%'        ,'Banking'       , 'Confidential'     ,1),
('%american%express%'        ,'Credit Card'      , 'Confidential'     ,1),
('%americanexpress%'      ,'Credit Card'      , 'Confidential'     ,1),
('%americano%espresso%'      ,'Credit Card'      , 'Confidential'     ,1),
('%amex%'           ,'Credit Card'      , 'Confidential'     ,1),
('%atm%card%'          ,'Credit Card'      , 'Confidential'     ,1),
('%atm%cards%'         ,'Credit Card'      , 'Confidential'     ,1),
('%atm%kaart%'         ,'Credit Card'      , 'Confidential'     ,1),
('%atmcard%'        ,'Credit Card'      , 'Confidential'     ,1),
('%atmcards%'          ,'Credit Card'      , 'Confidential'     ,1),
('%carte%bancaire%'       ,'Credit Card'      , 'Confidential'     ,1),
('%atmkaarten%'        ,'Credit Card'      , 'Confidential'     ,1),
('%bancontact%'        ,'Credit Card'      , 'Confidential'     ,1),
('%bank%card%'         ,'Credit Card'      , 'Confidential'     ,1),
('%bankkaart%'         ,'Credit Card'      , 'Confidential'     ,1),
('%card%holder%'       ,'Credit Card'      , 'Confidential'     ,1),
('%card%num%'          ,'Credit Card'      , 'Confidential'     ,1),
('%card%type%'         ,'Credit Card'      , 'Confidential'     ,1),
('%cardano%numerico%'        ,'Credit Card'      , 'Confidential'     ,1),
('%carta%bianca%'         ,'Credit Card'      , 'Confidential'     ,1),
('%carta%credito%'        ,'Credit Card'      , 'Confidential'     ,1),
('%carta%di%credito%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cartao%de%credito%'       ,'Credit Card'      , 'Confidential'     ,1),
('%cartao%de%crédito%'       ,'Credit Card'      , 'Confidential'     ,1),
('%cartao%de%debito%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cartao%de%débito%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cirrus%'         ,'Credit Card'      , 'Confidential'     ,1),
('%carte%blanche%'        ,'Credit Card'      , 'Confidential'     ,1),
('%carte%bleue%'       ,'Credit Card'      , 'Confidential'     ,1),
('%carte%de%credit%'      ,'Credit Card'      , 'Confidential'     ,1),
('%carte%de%crédit%'      ,'Credit Card'      , 'Confidential'     ,1),
('%carte%di%credito%'        ,'Credit Card'      , 'Confidential'     ,1),
('%carteblanche%'         ,'Credit Card'      , 'Confidential'     ,1),
('%cartão%de%credito%'       ,'Credit Card'      , 'Confidential'     ,1),
('%cartão%de%crédito%'       ,'Credit Card'      , 'Confidential'     ,1),
('%cartão%de%debito%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cartão%de%débito%'        ,'Credit Card'      , 'Confidential'     ,1),
('%check%card%'        ,'Credit Card'      , 'Confidential'     ,1),
('%chequekaart%'       ,'Credit Card'      , 'Confidential'     ,1),
('%hoofdkaart%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cirrus-edc-maestro%'      ,'Credit Card'      , 'Confidential'     ,1),
('%controlekaart%'        ,'Credit Card'      , 'Confidential'     ,1),
('%credit%card%'       ,'Credit Card'      , 'Confidential'     ,1),
('%debet%kaart%'       ,'Credit Card'      , 'Confidential'     ,1),
('%debit%card%'        ,'Credit Card'      , 'Confidential'     ,1),
('%debito%automatico%'       ,'Credit Card'      , 'Confidential'     ,1),
('%diners%club%'       ,'Credit Card'      , 'Confidential'     ,1),
('%discover%'          ,'Credit Card'      , 'Confidential'     ,1),
('%discover%card%'        ,'Credit Card'      , 'Confidential'     ,1),
('%débito%automático%'       ,'Credit Card'      , 'Confidential'     ,1),
('%eigentümername%'       ,'Credit Card'      , 'Confidential'     ,1),
('%european%debit%card%'     ,'Credit Card'      , 'Confidential'     ,1),
('%master%card%'       ,'Credit Card'      , 'Confidential'     ,1),
('%hoofdkaarten%'         ,'Credit Card'      , 'Confidential'     ,1),
('%in%viaggio%'        ,'Credit Card'      , 'Confidential'     ,1),
('%japanese%card%bureau%'       ,'Credit Card'      , 'Confidential'     ,1),
('%japanse%kaartdienst%'     ,'Credit Card'      , 'Confidential'     ,1),
('%jcb%'         ,'Credit Card'      , 'Confidential'     ,1),
('%kaart%'          ,'Credit Card'      , 'Confidential'     ,1),
('%kaart%num%'         ,'Credit Card'      , 'Confidential'     ,1),
('%kaartaantal%'       ,'Credit Card'      , 'Confidential'     ,1),
('%kaarthouder%'       ,'Credit Card'      , 'Confidential'     ,1),
('%karte%'          ,'Credit Card'      , 'Confidential'     ,1),
('%karteninhaber%'        ,'Credit Card'      , 'Confidential'     ,1),
('%kartennr%'          ,'Credit Card'      , 'Confidential'     ,1),
('%kartennummer%'         ,'Credit Card'      , 'Confidential'     ,1),
('%kreditkarte%'       ,'Credit Card'      , 'Confidential'     ,1),
('%maestro%'        ,'Credit Card'      , 'Confidential'     ,1),
('%numero%de%carte%'      ,'Credit Card'      , 'Confidential'     ,1),
('mc'            ,'Credit Card'      , 'Confidential'     ,1),
('%mister%cash%'       ,'Credit Card'      , 'Confidential'     ,1),
('%n%carta%'        ,'Credit Card'      , 'Confidential'     ,1),
('%n.%carta%'          ,'Credit Card'      , 'Confidential'     ,1),
('%no%de%tarjeta%'        ,'Credit Card'      , 'Confidential'     ,1),
('%no%do%cartao%'         ,'Credit Card'      , 'Confidential'     ,1),
('%no%do%cartão%'         ,'Credit Card'      , 'Confidential'     ,1),
('%no.%de%tarjeta%'       ,'Credit Card'      , 'Confidential'     ,1),
('%no.%do%cartao%'        ,'Credit Card'      , 'Confidential'     ,1),
('%no.%do%cartão%'        ,'Credit Card'      , 'Confidential'     ,1),
('%nr%carta%'          ,'Credit Card'      , 'Confidential'     ,1),
('%nr.%carta%'         ,'Credit Card'      , 'Confidential'     ,1),
('%numeri%di%scheda%'        ,'Credit Card'      , 'Confidential'     ,1),
('%numero%carta%'         ,'Credit Card'      , 'Confidential'     ,1),
('%numero%de%cartao%'        ,'Credit Card'      , 'Confidential'     ,1),
('%número%de%cartao%'        ,'Credit Card'      , 'Confidential'     ,1),
('%numero%de%cartão%'        ,'Credit Card'      , 'Confidential'     ,1),
('%numero%de%tarjeta%'       ,'Credit Card'      , 'Confidential'     ,1),
('%numero%della%carta%'      ,'Credit Card'      , 'Confidential'     ,1),
('%numero%di%carta%'      ,'Credit Card'      , 'Confidential'     ,1),
('%numero%di%scheda%'        ,'Credit Card'      , 'Confidential'     ,1),
('%numero%do%cartao%'        ,'Credit Card'      , 'Confidential'     ,1),
('%numero%do%cartão%'        ,'Credit Card'      , 'Confidential'     ,1),
('%numéro%de%carte%'      ,'Credit Card'      , 'Confidential'     ,1),
('%nº%carta%'          ,'Credit Card'      , 'Confidential'     ,1),
('%nº%de%carte%'       ,'Credit Card'      , 'Confidential'     ,1),
('%nº%de%la%carte%'       ,'Credit Card'      , 'Confidential'     ,1),
('%nº%de%tarjeta%'        ,'Credit Card'      , 'Confidential'     ,1),
('%nº%do%cartao%'         ,'Credit Card'      , 'Confidential'     ,1),
('%nº%do%cartão%'         ,'Credit Card'      , 'Confidential'     ,1),
('%nº.%do%cartão%'        ,'Credit Card'      , 'Confidential'     ,1),
('%scoprono%le%schede%'      ,'Credit Card'      , 'Confidential'     ,1),
('%número%de%cartão%'        ,'Credit Card'      , 'Confidential'     ,1),
('%número%de%tarjeta%'       ,'Credit Card'      , 'Confidential'     ,1),
('%número%do%cartao%'        ,'Credit Card'      , 'Confidential'     ,1),
('%scheda%dell''assegno%'       ,'Credit Card'      , 'Confidential'     ,1),
('%scheda%dell''atmosfera%'     ,'Credit Card'      , 'Confidential'     ,1),
('%scheda%dell''atmosfera%'     ,'Credit Card'      , 'Confidential'     ,1),
('%scheda%della%banca%'      ,'Credit Card'      , 'Confidential'     ,1),
('%scheda%di%controllo%'     ,'Credit Card'      , 'Confidential'     ,1),
('%scheda%di%debito%'        ,'Credit Card'      , 'Confidential'     ,1),
('%scheda%matrice%'       ,'Credit Card'      , 'Confidential'     ,1),
('%schede%dell''atmosfera%'     ,'Credit Card'      , 'Confidential'     ,1),
('%schede%di%controllo%'     ,'Credit Card'      , 'Confidential'     ,1),
('%schede%di%debito%'        ,'Credit Card'      , 'Confidential'     ,1),
('%schede%matrici%'       ,'Credit Card'      , 'Confidential'     ,1),
('%scoprono%la%scheda%'      ,'Credit Card'      , 'Confidential'     ,1),
('%visa%plus%'         ,'Credit Card'      , 'Confidential'     ,1),
('%solo%'           ,'Credit Card'      , 'Confidential'     ,1),
('%supporti%di%scheda%'      ,'Credit Card'      , 'Confidential'     ,1),
('%supporto%di%scheda%'      ,'Credit Card'      , 'Confidential'     ,1),
('%switch%'         ,'Credit Card'      , 'Confidential'     ,1),
('%tarjeta%atm%'       ,'Credit Card'      , 'Confidential'     ,1),
('%tarjeta%credito%'      ,'Credit Card'      , 'Confidential'     ,1),
('%tarjeta%de%atm%'       ,'Credit Card'      , 'Confidential'     ,1),
('%tarjeta%de%credito%'      ,'Credit Card'      , 'Confidential'     ,1),
('%tarjeta%de%debito%'       ,'Credit Card'      , 'Confidential'     ,1),
('%tarjeta%debito%'       ,'Credit Card'      , 'Confidential'     ,1),
('%tarjeta%no%'        ,'Credit Card'      , 'Confidential'     ,1),
('%tarjetahabiente%'      ,'Credit Card'      , 'Confidential'     ,1),
('%tipo%della%scheda%'       ,'Credit Card'      , 'Confidential'     ,1),
('%ufficio%giapponese%della%scheda%'  ,'Credit Card'   , 'Confidential'     ,1),
('%v%pay%'          ,'Credit Card'      , 'Confidential'     ,1),
('%codice%di%verifica%'      ,'Credit Card'      , 'Confidential'     ,1),
('%visa%electron%'        ,'Credit Card'      , 'Confidential'     ,1),
('%visto%'          ,'Credit Card'      , 'Confidential'     ,1),
('%card%identification%number%'    ,'Credit Card'      , 'Confidential'     ,1),
('%card%verification%'       ,'Credit Card'      , 'Confidential'     ,1),
('%cardi%la%verifica%'       ,'Credit Card'      , 'Confidential'     ,1),
('cid'           ,'Credit Card'      , 'Confidential'     ,1),
('%cod%seg%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cod%seguranca%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cod%segurança%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cod%sicurezza%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cod.%seg%'          ,'Credit Card'      , 'Confidential'     ,1),
('%cod.%seguranca%'       ,'Credit Card'      , 'Confidential'     ,1),
('%cod.%segurança%'       ,'Credit Card'      , 'Confidential'     ,1),
('%cod.%sicurezza%'       ,'Credit Card'      , 'Confidential'     ,1),
('%codice%di%sicurezza%'     ,'Credit Card'      , 'Confidential'     ,1),
('%código%de%seguranca%'     ,'Credit Card'      , 'Confidential'     ,1),
('%codigo%'         ,'Credit Card'      , 'Confidential'     ,1),
('%codigo%de%seguranca%'     ,'Credit Card'      , 'Confidential'     ,1),
('%codigo%de%segurança%'     ,'Credit Card'      , 'Confidential'     ,1),
('%crittogramma%'         ,'Credit Card'      , 'Confidential'     ,1),
('%cryptogram%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cryptogramme%'         ,'Credit Card'      , 'Confidential'     ,1),
('%cv2%'         ,'Credit Card'      , 'Confidential'     ,1),
('%cvc%'         ,'Credit Card'      , 'Confidential'     ,1),
('%cvc2%'           ,'Credit Card'      , 'Confidential'     ,1),
('%cvn%'         ,'Credit Card'      , 'Confidential'     ,1),
('%cód%seguranca%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cód%segurança%'        ,'Credit Card'      , 'Confidential'     ,1),
('%cód.%seguranca%'       ,'Credit Card'      , 'Confidential'     ,1),
('%cód.%segurança%'       ,'Credit Card'      , 'Confidential'     ,1),
('%código%'         ,'Credit Card'      , 'Confidential'     ,1),
('%numero%di%sicurezza%'     ,'Credit Card'      , 'Confidential'     ,1),
('%código%de%segurança%'     ,'Credit Card'      , 'Confidential'     ,1),
('%de%kaart%controle%'       ,'Credit Card'      , 'Confidential'     ,1),
('%geeft%nr%uit%'         ,'Credit Card'      , 'Confidential'     ,1),
('%issue%no%'          ,'Credit Card'      , 'Confidential'     ,1),
('%issue%number%'         ,'Credit Card'      , 'Confidential'     ,1),
('%kaartidentificatienummer%'      ,'Credit Card'      , 'Confidential'     ,1),
('%kreditkartenprufnummer%'     ,'Credit Card'      , 'Confidential'     ,1),
('%kreditkartenprüfnummer%'     ,'Credit Card'      , 'Confidential'     ,1),
('%kwestieaantal%'        ,'Credit Card'      , 'Confidential'     ,1),
('%no.%dell''edizione%'      ,'Credit Card'      , 'Confidential'     ,1),
('%no.%di%sicurezza%'        ,'Credit Card'      , 'Confidential'     ,1),
('%numero%de%securite%'      ,'Credit Card'      , 'Confidential'     ,1),
('%numero%de%verificacao%'      ,'Credit Card'      , 'Confidential'     ,1),
('%numero%dell''edizione%'      ,'Credit Card'      , 'Confidential'     ,1),
('%numero%di%identificazione%della%scheda%' ,'Credit Card' , 'Confidential'  ,1),
('%veiligheid%nr%'        ,'Credit Card'      , 'Confidential'     ,1),
('%numero%van%veiligheid%'      ,'Credit Card'      , 'Confidential'     ,1),
('%numéro%de%sécurité%'      ,'Credit Card'      , 'Confidential'     ,1),
('%nº%autorizzazione%'       ,'Credit Card'      , 'Confidential'     ,1),
('%número%de%verificação%'      ,'Credit Card'      , 'Confidential'     ,1),
('%perno%il%blocco%'      ,'Credit Card'      , 'Confidential'     ,1),
('%pin%block%'         ,'Credit Card'      , 'Confidential'     ,1),
('%prufziffer%'        ,'Credit Card'      , 'Confidential'     ,1),
('%prüfziffer%'        ,'Credit Card'      , 'Confidential'     ,1),
('%security%code%'        ,'Credit Card'      , 'Confidential'     ,1),
('%security%no%'       ,'Credit Card'      , 'Confidential'     ,1),
('%security%number%'      ,'Credit Card'      , 'Confidential'     ,1),
('%sicherheits%kode%'        ,'Credit Card'      , 'Confidential'     ,1),
('%sicherheitscode%'      ,'Credit Card'      , 'Confidential'     ,1),
('%sicherheitsnummer%'       ,'Credit Card'      , 'Confidential'     ,1),
('%speldblok%'         ,'Credit Card'      , 'Confidential'     ,1),
('%datum%van%exp%'        ,'Credit Card'      , 'Confidential'     ,1),
('%veiligheidsaantal%'       ,'Credit Card'      , 'Confidential'     ,1),
('%veiligheidscode%'      ,'Credit Card'      , 'Confidential'     ,1),
('%veiligheidsnummer%'       ,'Credit Card'      , 'Confidential'     ,1),
('%verfalldatum%'         ,'Credit Card'      , 'Confidential'     ,1),
('%ablauf%'         ,'Credit Card'      , 'Confidential'     ,1),
('%data%de%expiracao%'       ,'Credit Card'      , 'Confidential'     ,1),
('%data%de%expiração%'       ,'Credit Card'      , 'Confidential'     ,1),
('%data%del%exp%'         ,'Credit Card'      , 'Confidential'     ,1),
('%data%di%exp%'       ,'Credit Card'      , 'Confidential'     ,1),
('%data%di%scadenza%'        ,'Credit Card'      , 'Confidential'     ,1),
('%data%em%que%expira%'      ,'Credit Card'      , 'Confidential'     ,1),
('%data%scad%'         ,'Credit Card'      , 'Confidential'     ,1),
('%data%scadenza%'        ,'Credit Card'      , 'Confidential'     ,1),
('%date%de%validité%'        ,'Credit Card'      , 'Confidential'     ,1),
('%datum%afloop%'         ,'Credit Card'      , 'Confidential'     ,1),
('%de%afloop%'         ,'Credit Card'      , 'Confidential'     ,1),
('%datum%van%exp%'        ,'Credit Card'      , 'Confidential'     ,1),
('%espira%'         ,'Credit Card'      , 'Confidential'     ,1),
('%espira%'         ,'Credit Card'      , 'Confidential'     ,1),
('%exp%date%'          ,'Credit Card'      , 'Confidential'     ,1),
('%exp%datum%'         ,'Credit Card'      , 'Confidential'     ,1),
('%expiration%'        ,'Credit Card'      , 'Confidential'     ,1),
('%expire%'         ,'Credit Card'      , 'Confidential'     ,1),
('%expires%'        ,'Credit Card'      , 'Confidential'     ,1),
('%expiry%'         ,'Credit Card'      , 'Confidential'     ,1),
('%fecha%de%expiracion%'     ,'Credit Card'      , 'Confidential'     ,1),
('%fecha%de%venc%'        ,'Credit Card'      , 'Confidential'     ,1),
('%gultig%bis%'        ,'Credit Card'      , 'Confidential'     ,1),
('%gultigkeitsdatum%'        ,'Credit Card'      , 'Confidential'     ,1),
('%gültig%bis%'        ,'Credit Card'      , 'Confidential'     ,1),
('%gültigkeitsdatum%'        ,'Credit Card'      , 'Confidential'     ,1),
('%Fuehrerschein%'        ,'National ID'      , 'Confidential - GDPR'    ,1),
('%scadenza%'          ,'Credit Card'      , 'Confidential'     ,1),
('%valable%'        ,'Credit Card'      , 'Confidential'     ,1),
('%validade%'          ,'Credit Card'      , 'Confidential'     ,1),
('%valido%hasta%'         ,'Credit Card'      , 'Confidential'     ,1),
('%valor%'          ,'Credit Card'      , 'Confidential'     ,1),
('%venc%'           ,'Credit Card'      , 'Confidential'     ,1),
('%vencimento%'        ,'Credit Card'      , 'Confidential'     ,1),
('%vencimiento%'       ,'Credit Card'      , 'Confidential'     ,1),
('%verloopt%'          ,'Credit Card'      , 'Confidential'     ,1),
('%vervaldag%'         ,'Credit Card'      , 'Confidential'     ,1),
('%vervaldatum%'       ,'Credit Card'      , 'Confidential'     ,1),
('%vto%'         ,'Credit Card'      , 'Confidential'     ,1),
('%válido%hasta%'         ,'Credit Card'      , 'Confidential'     ,1),
('%Führerschein%'         ,'National ID'      , 'Confidential - GDPR'    ,1),
('%Fuhrerschein%'         ,'National ID'      , 'Confidential - GDPR'    ,1),
('%Fuehrerschein%'        ,'National ID'      , 'Confidential - GDPR'    ,1),
('%insee%'          ,'SSN'        , 'Confidential - GDPR'    ,1),
('%securité%sociale%'        ,'SSN'        , 'Confidential - GDPR'    ,1),
('%securite%sociale%'        ,'SSN'        , 'Confidential - GDPR'    ,1),
('%numéro%identité%'      ,'National ID'      , 'Confidential - GDPR'    ,1),
('%no%identité%'       ,'National ID'      , 'Confidential - GDPR'    ,1),
('%no.%identité%'         ,'National ID'      , 'Confidential - GDPR'    ,1),
('%numero%identite%'      ,'National ID'      , 'Confidential - GDPR'    ,1),
('%no%identite%'       ,'National ID'      , 'Confidential - GDPR'    ,1),
('%no.%identite%'         ,'National ID'      , 'Confidential - GDPR'    ,1),
('%le%numéro%d''identification%nationale%','National ID','Confidential - GDPR',1),
('%identité%nationale%'      ,'National ID'      , 'Confidential - GDPR'    ,1),
('%numéro%de%sécurité%sociale%'    ,'SSN'        , 'Confidential - GDPR'    ,1),
('%le%code%de%la%sécurité%sociale%'   ,'SSN'     , 'Confidential - GDPR'    ,1),
('%numéro%d''assurance%sociale%'   ,'SSN'        , 'Confidential - GDPR'    ,1),
('%numéro%de%sécu%'       ,'SSN'        , 'Confidential - GDPR'    ,1),
('%code%sécu%'         ,'SSN'        , 'Confidential - GDPR'    ,1),
('%reisepass%'         ,'National ID'      , 'Confidential - GDPR'    ,1),
('%passeport%'         ,'National ID'      , 'Confidential - GDPR'    ,1),
('%Personalausweis%'      ,'National ID'      , 'Confidential - GDPR'    ,1),
('%Identifizierungsnummer%'     ,'National ID', 'Confidential - GDPR'    ,1),
('%Ausweis%'        ,'National ID'      , 'Confidential - GDPR'    ,1),
('%Identifikation%'       ,'National ID'      , 'Confidential - GDPR'    ,1),
('%patente%di%guida%'        ,'National ID'      , 'Confidential - GDPR'    ,1)

DECLARE @InfoTypeRanking TABLE
(
info_type     NVARCHAR(128),
ranking    INT
)

INSERT INTO @InfoTypeRanking (info_type, ranking)
VALUES
('Banking',      800),
('Contact Info',    200),
('Credentials',     300),
('Credit Card',     700),
('Date Of Birth',   1100),
('Financial',    900),
('Health',       1000),
('Name',      400),
('National ID',     500),
('Networking',      100),
('SSN',       600),
('Other',     1200)

DECLARE @ClassifcationResults TABLE
(
schema_name   NVARCHAR(128),
table_name    NVARCHAR(128),
column_name   NVARCHAR(128),
info_type     NVARCHAR(128),
sensitivity_label   NVARCHAR(128),
ranking    INT,
can_be_numeric   BIT
)

INSERT INTO @ClassifcationResults
SELECT   DISTINCT 
S.NAME     AS schema_name,
T.NAME     AS table_name,
C.NAME     AS column_name,
D.info_type,
D.sensitivity_label,
R.ranking,
D.can_be_numeric
FROM  sys.schemas S
INNER JOIN  sys.tables T
ON    S.schema_id = T.schema_id
INNER JOIN  sys.columns C
ON    T.object_id = C.object_id
INNER JOIN  sys.types   TP
ON    C.system_type_id = TP.system_type_id
LEFT OUTER JOIN @Dictionary D
ON    (D.pattern NOT LIKE '%[%]%' AND LOWER(C.name) = LOWER(D.pattern) COLLATE DATABASE_DEFAULT) OR
(D.pattern LIKE '%[%]%' AND LOWER(C.name) LIKE LOWER(D.pattern) COLLATE DATABASE_DEFAULT)
LEFT OUTER JOIN @infoTypeRanking R
ON    (R.info_type = D.info_type)
WHERE    (D.info_type IS NOT NULL ) AND
NOT (D.can_be_numeric = 0 AND TP.name IN ('bigint','bit','decimal','float','int','money','numeric','smallint','smallmoney','tinyint'))

-- QUERY to apply the dictionary matches to the columns
SELECT DISTINCT
CR.schema_name AS schema_name,
CR.table_name AS table_name,
CR.column_name AS column_name,
CR.info_type AS information_type_name,
CR.sensitivity_label AS sensitivity_label_name
FROM @ClassifcationResults CR
INNER JOIN
(
SELECT
schema_name,
table_name,
column_name,
MIN(ranking) AS min_ranking
FROM
@ClassifcationResults
GROUP BY
schema_name,
table_name,
column_name
) MR
ON CR.schema_name = MR.schema_name
AND CR.table_name = MR.table_name
AND CR.column_name = MR.column_name
AND CR.Ranking = MR.min_ranking
ORDER BY schema_name, table_name, column_name