Tagging Personal Data Columns With ADD SENSITIVITY CLASSIFICATION

Knowing where personal data lives is an inventory task before it is a security setting. Sensitivity classification records reviewed labels on columns. Those labels describe data; they do not prevent somebody from reading it.

Discover Candidates Without Declaring Them Sensitive

Column names provide a useful first pass. Email, phone, and birth-related names can point toward fields requiring review. Names can also mislead: a contact field can hold an office address, and an unrelated label can hide personal data. A candidate report is not a completed privacy assessment.

I separate candidate discovery from the decision to label a column. That keeps a name pattern from becoming an automatic business verdict. Review the table's purpose, actual data contract, and authorized owner. A colored label cannot tell you what an undocumented application stores tomorrow.

The following discovery query uses REGEXP_LIKE in SQL Server 2025 with database compatibility level 170. Confirm the environment before running it. Do not change a production compatibility level simply to run a candidate inventory. A simpler LIKE-based review can serve older deployments.

SELECT compatibility_level FROM sys.databases WHERE database_id=DB_ID();
SELECT s.name AS SchemaName,t.name AS TableName,c.name AS ColumnName,
       ty.name AS DataType
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id=t.schema_id
JOIN sys.columns c ON c.object_id=t.object_id
JOIN sys.types ty ON ty.user_type_id=c.user_type_id
WHERE t.is_ms_shipped=0
 AND REGEXP_LIKE(c.name,'email|phone|birth','i')
ORDER BY s.name,t.name,c.column_id;

Create a Harmless Labeling Example

Use a disposable table with descriptive column names and no real personal data. The purpose is to demonstrate metadata operations, so the table does not need populated customer records. That also keeps the example independent of any person's contact details.

CREATE TABLE dbo.ClassificationReviewDemo
(
 ContactID int NOT NULL PRIMARY KEY,
 EmailAddress nvarchar(200) NULL,
 PhoneNumber nvarchar(40) NULL,
 BirthDate date NULL,
 ReferenceCode nvarchar(40) NULL
);

Before labeling a production column, agree on the organization's vocabulary. Label and INFORMATION_TYPE are separate metadata fields. A sensitivity label describes the chosen sensitivity category, while information type describes the kind of information. Consistent terms make later reports comparable across databases.

Do not choose HIGH only because the column contains the word Email. Ranking should follow the reviewed classification policy. The example uses simple descriptive values to show syntax, not to establish a universal legal or organizational classification.

Add the Reviewed Sensitivity Classification

ADD SENSITIVITY CLASSIFICATION associates metadata with a specified column. Its rank can be NONE, LOW, MEDIUM, HIGH, or CRITICAL. Use an approved rank and vocabulary for actual columns. The following commands label two sample columns separately for clarity.

ADD SENSITIVITY CLASSIFICATION TO dbo.ClassificationReviewDemo.EmailAddress
WITH (LABEL='Confidential', INFORMATION_TYPE='Contact details', RANK=HIGH);
ADD SENSITIVITY CLASSIFICATION TO dbo.ClassificationReviewDemo.PhoneNumber
WITH (LABEL='Confidential', INFORMATION_TYPE='Contact details', RANK=HIGH);

This changes metadata rather than stored column values. It does not rewrite data or create an encryption key. It also does not grant or revoke SELECT permission. Treat the labeling operation as an inventory change with a clear reviewing owner.

Check required classification permissions under the administrative process used for the database. Do not give an application login broad schema permissions to maintain this metadata. Discovery and reviewed administration can use different identities with different access requirements.

Read the Labels Back From the Catalog

Join sys.sensitivity_classifications to tables and columns to produce a readable inventory. Object and column identifiers are the stored relationship keys. Names are presentation fields and can change. Read back the metadata after applying labels to confirm the intended columns were selected.

SELECT s.name AS SchemaName,t.name AS TableName,c.name AS ColumnName,
       sc.label,sc.information_type,sc.rank_desc
FROM sys.sensitivity_classifications sc
JOIN sys.tables t ON t.object_id=sc.major_id
JOIN sys.schemas s ON s.schema_id=t.schema_id
JOIN sys.columns c ON c.object_id=sc.major_id AND c.column_id=sc.minor_id
WHERE sc.class=1
ORDER BY s.name,t.name,c.column_id;

An unlabeled column is not automatically non-sensitive. It can be awaiting review or missed by the candidate search. Similarly, a labeled column can contain a data type whose actual usage changed. Keep review status and ownership in the administrative inventory instead of interpreting label presence as a complete audit.

Remove an Incorrect Label Deliberately

DROP SENSITIVITY CLASSIFICATION removes the column's classification metadata. It does not drop the column or delete its data. Demonstrate it on one sample column, then rerun the catalog query to verify the intended metadata change.

DROP SENSITIVITY CLASSIFICATION
FROM dbo.ClassificationReviewDemo.PhoneNumber;
SELECT label,information_type,rank_desc
FROM sys.sensitivity_classifications
WHERE major_id=OBJECT_ID(N'dbo.ClassificationReviewDemo')
 AND minor_id=COLUMNPROPERTY
   (OBJECT_ID(N'dbo.ClassificationReviewDemo'),'PhoneNumber','ColumnId');

Record why a production label was removed. The next reviewer needs to distinguish an intentional correction from an accidentally omitted column. Keep the previous approved classification in the change record so it can be restored if the decision was wrong.

I verify the exact object and column before applying or removing metadata. Which reviewed requirement changed, and who owns that decision? A label cleanup should have the same concrete explanation as a label addition.

Pair Sensitivity Classification With Real Controls

Permissions determine who can access a column or its containing objects. Encryption protects data under its particular threat model. Masking changes the displayed values for eligible access paths, and auditing records configured activity. Each control has its own setup and limitations.

Classification can inform those decisions and help describe sensitive result sets in supported auditing scenarios. It does not enable all protections automatically. Do not tell readers that labeling a column makes the database compliant or secure. That claim would confuse documentation with enforcement.

Review exported data and application behavior too. A carefully labeled database can still send complete values to an over-permissive application or an uncontrolled report. The metadata inventory helps locate those questions; the answers require reviewing the actual access and handling processes.

Keep Sensitivity Classification Current

Schema changes introduce new columns, rename existing columns, and repurpose data. Re-run candidate discovery as part of a reviewed maintenance process. Compare candidates with current classifications and retain unresolved items until their owners respond.

Do not infer all sensitivity from datatype either. An integer can identify a person through a related table, while a long text field can contain unexpected details. Review relationships and free-text usage with the data owner. The short name pattern deliberately starts a conversation rather than finishing it.

Capture the database identity and review date with exported inventory rows. Classification metadata belongs to the database, but an external report can become stale. A current catalog read and a dated decision record together provide a clearer account than a spreadsheet copied indefinitely without verification.

A useful discovery report also includes columns that require manual review even when their names do not match. Keep that list alongside the automatic candidates. Reviewers can then see both the pattern's successes and its blind spots. A short regular expression is a convenient filter, but its silence should never be mistaken for an assurance about the rest of the schema.

Related reading on this blog: Dynamic Data Masking (DDM) Introduction and Keeping Personal Data Out of Test Databases.

Sensitivity classification is not data protection by itself, it is reviewed metadata that helps direct the controls and handling the data needs.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.