Data Protection

Overview

The Data Protection report answers one question for one database: the columns that hold sensitive data, what is actually protecting them?

The Sensitive Data report finds columns that look like personal or payment data, and TDE Status covers encryption at rest. This page shows the protections applied to the columns themselves:

  • Always Encrypted columns, their column encryption keys, and the column master key with its key store and path.
  • Dynamic data masking: every masked column with its masking function, and who can read it unmasked.
  • Row-level security policies, whether each is on, and their filter and block predicates.
  • Sensitivity classifications stored in the database (native from SQL Server 2019, and the extended properties SSMS wrote on older versions).
  • Ledger tables (SQL Server 2022).

Only metadata is read. The page never reads an encrypted or masked value.

Which columns count as sensitive

A column is sensitive when any of these is true:

  • It is classified in the database.
  • It is encrypted with Always Encrypted or masked (someone already decided it needed it).
  • Its name matches a Sensitive Data rule, using the types switched on and the custom patterns in Sensitive Data Options, and it was not dismissed on the Sensitive Data page.

Each sensitive column is counted once, under its strongest protection:

Protection Meaning
Encrypted Always Encrypted. The server never sees the plain value.
Masked Dynamic data masking. Users without UNMASK see a masked value.
Classified only Labeled with a sensitivity classification, but not encrypted or masked.
Unprotected Looks sensitive and is neither encrypted, masked nor classified.

The chart

The cards across the top count the sensitive columns and each protection, the row-level security policies (and how many are off), the column master keys (and how many live in a Windows certificate store), and the ledger tables. The bar under them splits the sensitive columns by protection. Click a segment or a coverage card to list just those columns in the Sensitive Columns view; pick Sensitive Columns again to show them all.

The findings are listed under the bar.

Findings

Finding When
Sensitive column with no protection A table has sensitive columns that are neither encrypted, masked nor classified. One finding per table, naming the columns.
RLS policy disabled A security policy exists but is STATE = OFF, so it filters and blocks nothing.
Masked column readable because users have UNMASK An explicit UNMASK grant (database, schema, table or column level) lets a user or role read masked columns in the clear. One finding per grantee.
Column master key in a Windows certificate store only The key is a certificate in a Windows certificate store. Every client needs a copy, and losing the certificate loses the data.

Information lines also note members of db_owner (who always read masked data unmasked), a policy with no predicates, dropped ledger tables that are kept, and ledger tables with no automatic digest storage. A part of the read that the login is refused is listed with what the server said.

Views

View What it lists
Findings Every finding with its detail.
Sensitive Columns Each sensitive column with its protection, information type, why it counts as sensitive, label, rank, mask and encryption. Unprotected columns first.
Always Encrypted Encrypted columns with their encryption type and keys, then the column master keys with the key store, key path, whether enclave computations are allowed, and how many columns each key encrypts.
Masked Masked columns with their function and who can read them unmasked, then the UNMASK grants.
Row-Level Security Each policy and predicate: state, filter or block, operation, target table, predicate text.
Classifications Each classified column with label, information type, rank and where it is stored.
Ledger Updatable and append-only ledger tables with their ledger view and history table.

Versions

Feature Needs
Always Encrypted, dynamic data masking, row-level security SQL Server 2016
Native sensitivity classifications (sys.sensitivity_classifications) SQL Server 2019
Ledger tables SQL Server 2022
UNMASK at schema, table or column level SQL Server 2022

A view whose feature is newer than the instance says Not available on this version. On SQL Server 2014 and older the page shows only that message and the Sensitive Data button.

Right-click actions

Item What it does
Script Mask For a sensitive column that is not masked or encrypted, an ALTER TABLE ... ADD MASKED WITH script with a suggested function (email, partial or default).
Script Enable For a disabled security policy, ALTER SECURITY POLICY ... WITH (STATE = ON).
Script Revoke UNMASK For an UNMASK grant, the matching REVOKE.
Open Sensitive Data The Sensitive Data page for this database, to classify columns.
Copy the findings as text The coverage and every finding.

Scripts are shown, never run. The grid also has the usual Go to menu, CSV and Excel export.

Permissions

The page reads catalog views only. Without VIEW DEFINITION a login sees only the objects it has permission on, so the counts may be low; the page says so. The UNMASK and db_owner information comes from sys.database_permissions and sys.database_role_members.

Report Why you would go there
Sensitive Data Which columns look sensitive, and classify them.
Data Protection by Database The same counts for every database on the instance.
TDE Status Encryption at rest for the whole database.
Permissions Matrix Who can reach the tables.