Skip to content

sys-sensitivity-classifications-transact-sql.md

Latest commit

 

History

History
83 lines (68 loc) · 4.01 KB

sys-sensitivity-classifications-transact-sql.md

File metadata and controls

83 lines (68 loc) · 4.01 KB
title sys.sensitivity_classifications (Transact-SQL) | Microsoft Docs
ms.date 03/25/2019
ms.reviewer
ms.prod sql
ms.technology t-sql
ms.topic language-reference
ms.custom
ms.author mibar
author barmichal
f1_keywords
sys.sensitivity_classifications
dev_langs
TSQL
helpviewer_keywords
sys.sensitivity_classifications statement
dropping labels
drop labels
removing labels
remove labels
classification [SQL]
labels [SQL]
information types
rank
monikerRange = azuresqldb-current || = sqlallproducts-allversions

sys.sensitivity_classifications (Transact-SQL)

[!INCLUDEtsql-appliesto-xxxxxx-asdb-asdw-xxx-md]

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 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

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