Sensitive Data
Overview
The Sensitive Data report answers the question every PCI DSS, HIPAA or privacy review starts with: where is the personal and payment data in this database, and is it classified?
It uses two signals:
- Column names, always. Every column name is split into words (CustomerSSN is customer ssn, e_mail is e mail) and matched against patterns for email, phone, address, name, date of birth, US Social Security number, payment card number, bank account and IBAN, national ID, passwords and other credentials, health data, financial data and IP addresses. The data type has to fit too: a column called EmailSentFlag that is a
bitis not flagged. - Column values, only when you turn it on. With value sampling on, a small sample of values from each candidate column is tested in memory: email format, US SSN (with the ranges that are never issued left out), payment card numbers (13 to 19 digits, a known issuer prefix and a passing Luhn check), IBAN (mod 97 check), US phone numbers and IPv4 addresses.
Privacy
Sampled values are never stored, displayed, logged, exported or included in error messages. Each value is read into memory, tested, counted and dropped. The grid, the export, the history database and the evidence text only ever hold counts, such as “47 of 100 sampled values match SSN pattern”. A sample query that fails is reported by its SQL error number and the table name only, because some SQL Server error messages quote the value that failed.
Where to find it
A database-level report in the Real Time group. Select a database in the tree, then open Sensitive Data.
The page opens on the existing classifications only (metadata, fast on any size of database), read from sys.sensitivity_classifications on SQL Server 2019 and later and from the extended properties SSMS writes on SQL Server 2012 to 2017. Press Scan to look for columns that are not classified yet.
The toolbar
| Button | What it does |
|---|---|
Scan |
Matches every column name, and samples values when sampling is on. Runs in the background; Cancel stops it and the page says the results are partial. |
Options ... |
Value sampling and its limits, the kinds of data to look for, and custom name patterns. |
All · Suggested · Classified · Dismissed |
Filters the grid without scanning again. |
Apply Selected Classifications ... |
Opens a preview of the T-SQL for the selected rows. Nothing runs until you press Run. |
Remove Classification ... |
The same preview, dropping the classification of the selected rows. |
Export |
Save the grid as CSV or HTML, or copy it. Only metadata columns exist, so there is nothing sensitive to export. |
Options
| Option | Default |
|---|---|
| Sample column values | Off. The first time you turn it on you are asked to confirm. |
| Sample size per column | 100 (maximum 1000) |
| Maximum tables to sample | 500 |
| Maximum scan time | 5 minutes |
| Skip tables larger than | 10 GB |
| Use READ UNCOMMITTED | On, so sampling never blocks or waits on writers. Turned off, a table that is locked times out after 30 seconds and is reported as not sampled. |
| Look for | Every kind of data, each one can be turned off. |
| Custom name patterns | One per line: regular expression => Information Type, for example ^cust_ref$ => National ID. |
Only character columns up to 4000 characters and whole number columns are sampled. Identity and computed columns never are.
Reading the grid
| Column | What it is |
|---|---|
| Status | Suggested, Classified, Classified differently (the suggestion disagrees with the current classification) or Dismissed. (edited) marks a row whose classification you changed with Edit. |
| Confidence | High: the name matched and at least half of the sampled values match. Medium: the name matched, or at least 80 percent of the values match. Low: no name match, but at least 20 percent of the values match. |
| Schema, Table, Column, Data Type | The column. |
| Suggested Type, Suggested Label | What Apply will write. The information types and labels are the ones SSMS uses, so classifications look the same in both tools. |
| Current Label, Current Type, Current Rank | How the column is classified today. |
| Rows (approx) | From the partition row counts. |
| Evidence | Why the row is here, in counts and names only. |
The band above the grid summarizes the scan, for example: 212 columns scanned in 38 tables. 14 suggested (6 High), 9 already classified, 3 classified differently from suggestion. It also says when the scan was partial (time limit, table limit or Cancel).
Right-click actions
| Item | What it does |
|---|---|
| Accept Suggestion … | Opens the Apply preview for the selected rows. |
| Edit Classification … | Choose the label, information type and (SQL Server 2019 and later) rank. Double-clicking a row does the same. |
| Dismiss Suggestion … | Hides a suggestion you have checked, with an optional reason. Dismissals are remembered and stay dismissed on later scans. |
| Restore Dismissed Suggestion | Takes a dismissal back. |
| Remove Classification … | Drops the current classification. |
| Go to | The table’s other pages. |
Applying classifications
The Apply dialog shows the whole script with Run, Copy Script and Save Script …. Each column runs on its own and gets its own OK or error, so one failing column does not stop the rest.
- SQL Server 2019 and later:
ADD SENSITIVITY CLASSIFICATION TO [schema].[table].[column] WITH (LABEL = ..., INFORMATION_TYPE = ..., RANK = ...). - SQL Server 2012 to 2017: the extended properties SSMS uses (
sys_information_type_name,sys_information_type_id,sys_sensitivity_label_name,sys_sensitivity_label_id). - SQL Server 2008 R2 and earlier: name scan only. Classification requires SQL Server 2012 or later (extended properties) or 2019 or later (native).
Names containing ] or ' are escaped. Applying needs ALTER ANY SENSITIVITY CLASSIFICATION (SQL Server 2019 and later) or permission to add extended properties.
Where the data comes from
sys.tables, sys.columns, sys.types, sys.partitions, sys.allocation_units, sys.sensitivity_classifications and sys.extended_properties, plus, with sampling on, one SELECT TOP (n) per candidate table. When the instance has a history database, each scan’s metadata and counts are kept in dbo.SensitiveColumnScan (the latest scan and the one before it, per database); otherwise they are kept for the session.
Frequently asked questions
Does this page copy my data anywhere? No. Sampled values are tested in memory and dropped. Only counts are kept.
Why is a column called Email not flagged? Its type does not fit (a bit or a uniqueidentifier), or its name says it describes an email rather than holding one (EmailSentFlag, EmailTemplate).
Will sampling slow down production? It reads at most the sample size of rows per table, stops at the table and time limits, skips large tables, and by default reads with READ UNCOMMITTED so it neither blocks nor waits.