SQL Server Data Classification: Discovery, Labeling, and Audit Integration

SQL Server Data Classification is a built-in feature for tagging database columns with sensitivity metadata. It does not encrypt data, does not block access, and does not move or mask any values. What it does is attach two structured metadata attributes to columns — a sensitivity label and an information type — and make those attributes available to audit logs, reporting tools, and governance platforms. When combined with SQL Server Audit (especially the SENSITIVE_BATCH_COMPLETED_GROUP action group introduced in SQL Server 2022), classification becomes a practical compliance and monitoring tool rather than just documentation.

Every classification applied to a column carries two independent metadata attributes. Both are free-text strings, so organizations define their own taxonomy, though Microsoft Purview provides a standard set that can be imported via SSMS.

A column can have both attributes set together, or only one. The command succeeds with either attribute alone, but both together provide the most value for compliance reporting. Each attribute also accepts an optional ID (LABEL_ID, INFORMATION_TYPE_ID) which is a GUID used by centralized governance platforms like Microsoft Purview to map labels across systems. The IDs are optional for on-premises-only implementations.

Sensitivity rank is a third optional attribute that provides a standardized numeric ordering of sensitivity independent of label name. Because different organizations use different label names (Restricted, Highly Confidential, Secret, Tier 3), rank provides a universal comparison value that external tools like Microsoft Defender for SQL can use to detect anomalies.

The ADD SENSITIVITY CLASSIFICATION statement applies metadata to one or more columns in a single call. Multiple columns from the same or different tables can be classified in one statement.

-- Classify a single column: email address as PII, Confidential
ADD SENSITIVITY CLASSIFICATION TO dbo.Customer.Email
WITH (
    LABEL            = 'Confidential',
    INFORMATION_TYPE = 'Email',
    RANK             = HIGH
);
GO

-- Classify multiple columns in one statement
ADD SENSITIVITY CLASSIFICATION TO
    dbo.Customer.FirstName,
    dbo.Customer.LastName,
    dbo.Customer.DateOfBirth
WITH (
    LABEL            = 'Confidential',
    INFORMATION_TYPE = 'Name',
    RANK             = HIGH
);
GO

-- Classify payment card data at Critical rank
ADD SENSITIVITY CLASSIFICATION TO
    dbo.Payment.CardNumber,
    dbo.Payment.CVV
WITH (
    LABEL            = 'Highly Confidential',
    INFORMATION_TYPE = 'Credit Card Number',
    RANK             = CRITICAL
);
GO

-- Classify with GUIDs for Purview integration
-- GUIDs match the label and information type IDs defined in Microsoft Purview
ADD SENSITIVITY CLASSIFICATION TO dbo.Employee.NationalID
WITH (
    LABEL             = 'Highly Confidential',
    LABEL_ID          = '3A477B16-9B93-4E5D-9A6A-5BEF9A4B9D1E',
    INFORMATION_TYPE  = 'National ID',
    INFORMATION_TYPE_ID = 'C64AEF1E-9B5D-4B4C-9B7A-8A5E9C2A4D3F',
    RANK              = CRITICAL
);
GO

SSMS 17.5 and later include a Data Discovery and Classification UI accessible under Tasks for any database. The UI runs a recommendation engine that scans column names for patterns matching known sensitive data types and produces a list of suggested classifications. Recommendations can be accepted individually or in bulk, then saved to the database.

To open the Data Classification pane in SSMS: right-click the database in Object Explorer, select Tasks, then Data Discovery and Classification, then Classify Data. The pane shows three sections: a recommendations bar at the top for columns the engine identified as potentially sensitive, the main grid showing currently classified columns, and an Add Classification button for manual entries.

When Microsoft Purview Information Protection policy mode is active for a connected Purview account, the SSMS recommendation engine is disabled. In that mode, labels come from Purview directly rather than from the local recommendation list.

To generate a classification report in SSMS after applying classifications: right-click the database, select Tasks, Data Discovery and Classification, Generate Report. The report exports as an Excel file or displays inline, listing every classified column with its label, information type, rank, and schema location.

All classifications are stored as metadata in the database and accessible through the sys.sensitivity_classifications catalog view. The view itself does not contain table or column names directly; those require joins to sys.all_objects and sys.all_columns.

-- List all classified columns in the current database
SELECT
    SCHEMA_NAME(o.schema_id)   AS schema_name,
    o.name                     AS table_name,
    c.name                     AS column_name,
    sc.label                   AS sensitivity_label,
    sc.information_type,
    sc.rank,
    sc.rank_desc
FROM sys.sensitivity_classifications sc
JOIN sys.all_objects  o ON sc.major_id = o.object_id
JOIN sys.all_columns  c ON sc.major_id = c.object_id
                       AND sc.minor_id = c.column_id
ORDER BY
    schema_name,
    table_name,
    column_name;
GO

-- Count classified columns by label for a compliance summary
SELECT
    sc.label                    AS sensitivity_label,
    sc.rank_desc,
    COUNT(*)                    AS column_count
FROM sys.sensitivity_classifications sc
GROUP BY sc.label, sc.rank_desc
ORDER BY sc.rank DESC;
GO

-- Find all CRITICAL rank columns across the database
-- Useful for prioritizing access control and masking reviews
SELECT
    SCHEMA_NAME(o.schema_id)   AS schema_name,
    o.name                     AS table_name,
    c.name                     AS column_name,
    sc.label,
    sc.information_type
FROM sys.sensitivity_classifications sc
JOIN sys.all_objects  o ON sc.major_id = o.object_id
JOIN sys.all_columns  c ON sc.major_id = c.object_id
                       AND sc.minor_id = c.column_id
WHERE sc.rank = 40   -- CRITICAL
ORDER BY schema_name, table_name, column_name;
GO

-- Find tables with classified columns that have no column-level permissions
-- Useful for identifying gaps between classification and access control
SELECT DISTINCT
    SCHEMA_NAME(o.schema_id)   AS schema_name,
    o.name                     AS table_name,
    sc.label
FROM sys.sensitivity_classifications sc
JOIN sys.all_objects  o ON sc.major_id = o.object_id
JOIN sys.all_columns  c ON sc.major_id = c.object_id
                       AND sc.minor_id = c.column_id
WHERE NOT EXISTS (
    SELECT 1
    FROM sys.database_permissions dp
    WHERE dp.major_id   = sc.major_id
      AND dp.minor_id   = sc.minor_id
      AND dp.class      = 1   -- object or column permission
      AND dp.state_desc = 'GRANT'
)
AND sc.rank >= 30   -- HIGH or CRITICAL only
ORDER BY schema_name, table_name;
GO

-- Remove classification from a single column
DROP SENSITIVITY CLASSIFICATION FROM dbo.Customer.Email;
GO

-- Remove classification from multiple columns in one statement
DROP SENSITIVITY CLASSIFICATION FROM
    dbo.Customer.FirstName,
    dbo.Customer.LastName;
GO

Removing a classification does not affect the data or any permissions on the column. It removes only the metadata stored in sys.sensitivity_classifications. Audit logs that captured access to the column while it was classified retain the sensitivity information in the data_sensitivity_information audit field for the retention period.

SQL Server Audit gained a significant data classification integration in SQL Server 2022: the SENSITIVE_BATCH_COMPLETED_GROUP action group. When this group is added to a database audit specification, SQL Server automatically captures any query that touches a classified column, including the sensitivity metadata of the columns accessed. This works regardless of how the data is accessed: direct SELECT, statistics objects, DMF access, or any other query path that reads classified column data.

-- Step 1: Create a server audit writing to the file system
CREATE SERVER AUDIT DataClassificationAudit
TO FILE (
    FILEPATH = 'C:\SQLAudit\',
    MAXSIZE   = 100 MB,
    MAX_ROLLOVER_FILES = 10,
    RESERVE_DISK_SPACE = OFF
)
WITH (
    QUEUE_DELAY    = 1000,
    ON_FAILURE     = CONTINUE
);
GO

ALTER SERVER AUDIT DataClassificationAudit WITH (STATE = ON);
GO

-- Step 2: Create a database audit specification
-- SENSITIVE_BATCH_COMPLETED_GROUP captures queries touching classified columns
USE YourDatabase;
GO

CREATE DATABASE AUDIT SPECIFICATION ClassifiedDataAccess
FOR SERVER AUDIT DataClassificationAudit
ADD (SENSITIVE_BATCH_COMPLETED_GROUP)
WITH (STATE = ON);
GO

-- Step 3: Query audit results to review classified data access
-- data_sensitivity_information contains XML with label and information type
SELECT
    event_time,
    server_principal_name    AS login_name,
    database_name,
    object_name,
    statement,
    CONVERT(XML, data_sensitivity_information) AS sensitivity_info
FROM sys.fn_get_audit_file(
    'C:\SQLAudit\DataClassificationAudit_*.sqlaudit',
    DEFAULT,
    DEFAULT
)
WHERE data_sensitivity_information IS NOT NULL
ORDER BY event_time DESC;
GO

The data_sensitivity_information column in audit results contains XML listing the sensitivity labels and information types of the classified columns touched by the query. An example of the XML structure:

<sensitivity_attributes>
  <sensitivity_attribute
      label="Highly Confidential"
      label_id="3A477B16-9B93-4E5D-9A6A-5BEF9A4B9D1E"
      information_type="Credit Card Number"
      information_type_id="C64AEF1E-9B5D-4B4C-9B7A-8A5E9C2A4D3F"
      rank="40" />
  <sensitivity_attribute
      label="Confidential"
      information_type="Email"
      rank="30" />
</sensitivity_attributes>

-- Parse the sensitivity XML to extract label details from audit records
SELECT
    event_time,
    server_principal_name                                       AS login_name,
    statement,
    x.query('sensitivity_attribute/@label').value('.', 'NVARCHAR(200)')
                                                               AS label,
    x.query('sensitivity_attribute/@information_type').value('.', 'NVARCHAR(200)')
                                                               AS information_type
FROM sys.fn_get_audit_file(
    'C:\SQLAudit\DataClassificationAudit_*.sqlaudit',
    DEFAULT,
    DEFAULT
)
CROSS APPLY (
    SELECT CONVERT(XML, data_sensitivity_information)
) AS t(xml_data)
CROSS APPLY xml_data.nodes('/sensitivity_attributes') AS n(x)
WHERE data_sensitivity_information IS NOT NULL
ORDER BY event_time DESC;
GO

Organizations using Microsoft Purview for enterprise data governance can synchronize sensitivity labels defined in the Microsoft 365 Compliance Center directly into SSMS, so the same label taxonomy used across Microsoft 365, Azure, and Power BI also applies to on-premises SQL Server data classification.

To import Purview labels into SSMS: in the Data Classification pane, select the option to fetch sensitivity labels from Microsoft Purview. SSMS authenticates to Microsoft 365 and retrieves the available labels. Once imported, classification assignments use the Purview-defined labels and their associated GUIDs, which creates a consistent label inventory across the entire data estate.

A compliance-ready classification report can be generated from SSMS or built from T-SQL for integration into automated reporting pipelines.

From SSMS

Right-click the database in Object Explorer, select Tasks, Data Discovery and Classification, Generate Report. The report shows every classified column with schema, table, column name, label, information type, and rank. It exports to Excel for inclusion in compliance documentation packages.

From T-SQL for automated reporting

-- Classification inventory report: all databases on the instance
-- Useful for a DBA running a compliance sweep across multiple databases
-- Run in each database context or adapt with dynamic SQL for cross-database

SELECT
    DB_NAME()                  AS database_name,
    SCHEMA_NAME(o.schema_id)   AS schema_name,
    o.name                     AS table_name,
    c.name                     AS column_name,
    sc.label,
    sc.information_type,
    sc.rank_desc               AS sensitivity_rank,
    GETDATE()                  AS report_generated_at
FROM sys.sensitivity_classifications sc
JOIN sys.all_objects  o ON sc.major_id = o.object_id
JOIN sys.all_columns  c ON sc.major_id = c.object_id
                       AND sc.minor_id = c.column_id
ORDER BY sc.rank DESC, schema_name, table_name, column_name;
GO

-- Summary by database for a multi-database compliance dashboard
-- Execute via sp_MSforeachdb or a cursor across target databases

SELECT
    DB_NAME()                  AS database_name,
    sc.rank_desc,
    COUNT(*)                   AS classified_columns,
    COUNT(DISTINCT sc.major_id) AS classified_tables
FROM sys.sensitivity_classifications sc
GROUP BY sc.rank_desc
ORDER BY MAX(sc.rank) DESC;
GO