sys.sensitivity_classifications (Transact-SQL)

APPLIES TO: noSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

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
class_desc varchar(16) A description of the class of the item on which the classification exists
major_id int ID of the item on which the classification exists.

If class is 0, major_id is always 0.
If class is 1, 2, or 7 major_id is object_id.
minor_id int Secondary ID of the item on which the classification exists, interpreted according to its class.

If class = 1, minor_id is the column_id (if column), else 0 (if object).
If class = 2, minor_id is the parameter_id.
If class = 7, minor_id is the index_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. Consequently:
    • class - will always have the value 1 (representing a column)
    • class_desc - will always have the value OBJECT_OR_COLUMN
    • major_id - represents the ID of the table containing the classified column, corresponding with sys.all_objects.object_id
    • minor_id - represents the ID of the column on which the classification exists, corresponding with sys.all_columns.column_id

Examples

A. Listing all classified columns and their corresponding classification

The following example returns a table listing the table name, column name, label, label ID, information type, information type ID for each classified column in the database.

Note

Label is a keyword for Azure SQL Data Warehouse.

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

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. 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