How to classify sensitive data in SQL Server

Knowing where your sensitive data lives is the first step of any data governance strategy. SQL Server lets you classify sensitive data directly inside the database engine, with no external tools, using the ADD SENSITIVITY CLASSIFICATION command. By labelling the columns that hold personal or confidential information, you get a reliable inventory of what needs protection and a solid basis for auditing who accesses that data.

Prerequisites

• SQL Server 2019 or later, or an Azure SQL Database (where the Data Discovery & Classification feature is available).
• SQL Server Management Studio (SSMS) or Azure Data Studio to run the commands.
• The ALTER ANY SENSITIVITY CLASSIFICATION permission, which is also granted by the CONTROL permission on the database.
• A test database where you can create tables without affecting production.

Step 1: Create a sample table

To practise classifying sensitive data in SQL Server, start by creating a simple table with columns that represent personal information, such as email, phone and tax number.

CREATE TABLE dbo.Customers
(
    CustomerID INT IDENTITY PRIMARY KEY,
    FullName   NVARCHAR(100),
    Email      NVARCHAR(255),
    Phone      NVARCHAR(20),
    TaxId      CHAR(9)
);

This table will be the target for the sensitivity labels in the next steps.

Step 2: Classify a column with ADD SENSITIVITY CLASSIFICATION

The ADD SENSITIVITY CLASSIFICATION command applies three attributes to a column: a label (LABEL) for the confidentiality level; an information type (INFORMATION_TYPE) describing the nature of the data; and a risk level (RANK). Let's classify the email column.

ADD SENSITIVITY CLASSIFICATION TO
    dbo.Customers.Email
WITH (
    LABEL = 'Confidential',
    INFORMATION_TYPE = 'Contact Info',
    RANK = MEDIUM
);

The RANK parameter accepts the values NONE, LOW, MEDIUM, HIGH and CRITICAL. It is useful because services such as Advanced Threat Protection use this level to detect anomalous access to more critical data.

The LABEL and INFORMATION_TYPE values are free text, so you can use your organisation's own classification scheme; even so, it is good practice to reuse consistent names (for example, Public, General, Confidential and Highly Confidential) to make reporting easier.

Step 3: Classify several columns at once

You can classify multiple columns in a single command by separating the objects with commas. Every listed column receives the same classification, which is handy for grouping columns of equal sensitivity.

ADD SENSITIVITY CLASSIFICATION TO
    dbo.Customers.Phone,
    dbo.Customers.TaxId
WITH (
    LABEL = 'Highly Confidential',
    INFORMATION_TYPE = 'Personal Data',
    RANK = HIGH
);

Keep an important rule in mind: each column can hold only one classification. If you run the command again on an already-classified column, the new label overwrites the previous one without raising an error.

Check the result

To confirm the classifications were applied, query the sys.sensitivity_classifications system view. Because this view only stores column identifiers, join it to sys.columns to see readable names.

SELECT
    OBJECT_NAME(sc.major_id) AS TableName,
    c.name                   AS ColumnName,
    sc.label,
    sc.information_type,
    sc.rank_desc
FROM sys.sensitivity_classifications AS sc
JOIN sys.columns AS c
    ON c.object_id = sc.major_id
   AND c.column_id = sc.minor_id;

You should get three rows — Email, Phone and TaxId — each with the label, information type and risk level you defined. Alternatively, in SSMS you can right-click the database and choose Tasks > Data Discovery and Classification to open a visual report with the same information.

Tip: to remove a classification, use DROP SENSITIVITY CLASSIFICATION FROM dbo.Customers.Email;

Conclusion

Classifying sensitive data in SQL Server gives you a clear map of the information that needs protection and sets the stage for auditing: once you enable SQL Server Audit, the logs start showing which classified data was accessed and by whom. The next step is to combine these labels with Dynamic Data Masking or Row-Level Security to actually protect the most critical columns. Look at your table: which columns, such as FullName, are still left unclassified?