Skip to content

Latest commit

 

History

History
81 lines (66 loc) · 4.07 KB

sys-sensitivity-classifications-transact-sql.md

File metadata and controls

81 lines (66 loc) · 4.07 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.sensitivity_classifications (Transact-SQL)
sys.sensitivity_classifications (Transact-SQL)
Madhumitatripathy
matripathy
04/19/2022
sql
t-sql
reference
sys.sensitivity_classifications
sys.sensitivity_classifications statement
dropping labels
drop labels
removing labels
remove labels
classification [SQL]
labels [SQL]
information types
rank
TSQL
>=sql-server-ver15||=azuresqldb-current||=azure-sqldw-latest

sys.sensitivity_classifications (Transact-SQL)

[!INCLUDE SQL Server ASDB, ASDBMI, ASDW]

Returns a row for each classified item in the database.

Column name Data type Description
class int Identifies the class of the item on which the classification exists. Will always have the value 1 (representing a column)
class_desc varchar(16) A description of the class of the item on which the classification exists. will always have the value OBJECT_OR_COLUMN
major_id int Represents the ID of the table containing the classified column, corresponding with sys.all_objects.object_id
minor_id int Represents the ID of the column on which the classification exists, corresponding with sys.all_columns.column_id
label sysname The label (human readable) assigned for the sensitivity classification
label_id sysname An ID associated with the label, which can be used by an information protection system such as Azure Information Protection (AIP)
information_type sysname The information type (human readable) assigned for the sensitivity classification
information_type_id sysname An ID associated with the information type, which can be used by an information protection system such as Azure Information Protection (AIP)
rank int A numerical value of the rank:

0 for NONE
10 for LOW
20 for MEDIUM
30 for HIGH
40 for CRITICAL
rank_desc sysname Textual representation of the rank:

NONE, LOW, MEDIUM, HIGH, CRITICAL

Remarks

  • This view provides visibility into the classification state of the database. It can be used for managing the database classifications, as well as for generating reports.
  • Currently only classification of database columns is supported.

Examples

A. Listing all classified columns and their corresponding classification

The following example returns a table that lists the table name, column name, label, label ID, information type, information type ID, rank, and rank description for each classified column in the database.

Note

Label is a keyword for Azure Synapse Analytics.

SELECT
    SCHEMA_NAME(sys.all_objects.schema_id) as SchemaName,
    sys.all_objects.name AS [TableName], sys.all_columns.name As [ColumnName],
    [Label], [Label_ID], [Information_Type], [Information_Type_ID], [Rank], [Rank_Desc]
FROM
          sys.sensitivity_classifications
left join sys.all_objects on sys.sensitivity_classifications.major_id = sys.all_objects.object_id
left join sys.all_columns on sys.sensitivity_classifications.major_id = sys.all_columns.object_id
                         and sys.sensitivity_classifications.minor_id = sys.all_columns.column_id

Permissions

Requires the VIEW ANY SENSITIVITY CLASSIFICATION permission.

[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.

See Also

ADD SENSITIVITY CLASSIFICATION (Transact-SQL)

DROP SENSITIVITY CLASSIFICATION (Transact-SQL)

Getting started with SQL Information Protection