Get-SqlSensitivityClassification

Module:: SqlServer Module

Get the sensitivity label and information type of columns in the database.

Syntax

ByContext (Default)

Get-SqlSensitivityClassification
    [-ColumnName <String[]>]
    [-SuppressProviderContextWarning]
    [<CommonParameters>]

ByConnectionString

Get-SqlSensitivityClassification
    -ConnectionString <String>
    [-ColumnName <String[]>]
    [<CommonParameters>]

ByConnectionParameters

Get-SqlSensitivityClassification
    -ServerInstance <PSObject>
    -DatabaseName <String>
    [-ColumnName <String[]>]
    [-Credential <PSCredential>]
    [<CommonParameters>]

ByPath

Get-SqlSensitivityClassification
    -Path <String>
    [-ColumnName <String[]>]
    [<CommonParameters>]

ByDBObject

Get-SqlSensitivityClassification
    -InputObject <Database>
    [-ColumnName <String[]>]
    [<CommonParameters>]

Description

The Get-SqlSensitivityClassification cmdlet gets the sensitivity labels and information types of columns in the database.

The sensitivity labels and information types of columns can also be viewed using SQL Server Management Studio (SSMS) release 17.5 and above, or the Extended Properties catalog view.

The sensitivity labels and information types of columns can be set using SQL Server Management Studio (SSMS) release 17.5 and above, or with the Set-SqlSensitivityClassification cmdlet.

Module requirements: version 21+ on PowerShell 5.1; version 22+ on PowerShell 7.x.

Examples

Example 1: Get all information types and sensitivity labels from a database using Windows authentication

PS C:\> Get-SqlSensitivityClassification -ServerInstance "MyComputer\MainInstance" -Database "myDatabase"

Column                      InformationType SensitivityLabel    SensitivityRank
------                      --------------- ----------------    ---------------
Sales.Customers.email       Contact Info    Confidential        Medium
Sales.Customers.first_name  Name            Confidential - GDPR Medium

This command gets the information type and sensitivity label of all columns in the database. The cmdlet returns only the columns that have an information type or a sensitivity label (or both) defined.

Example 2: Get information type and sensitivity label of a single column from database by providing path

PS C:\> Get-SqlSensitivityClassification -Path "SQLSERVER:\SQL\MyComputer\MainInstance\Databases\MyDatabase" -ColumnName "Sales.Customers.email"

Column                    InformationType SensitivityLabel SensitivityRank
------                    --------------- ---------------- ---------------
Sales.Customers.email     Contact Info    Confidential     Medium

This command gets the information type and sensitivity label of the Sales.Customers.email column in the database provided in the Path.

Example 3: Get sensitivity labels and information types of multiple columns using using current path context

PS C:\> $columns = @("Sales.Customers.ip_address" , "Sales.Customers.email")
PS C:\> Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance\Databases\MyDatabase"
PS SQLSERVER:\SQL\MyComputer\MainInstance> Get-SqlSensitivityClassification -ColumnName $columns
 WARNING: Using provider context. Server = MyComputer, Database = MyDatabase.

Column                      InformationType SensitivityLabel SensitivityRank
------                      --------------- ---------------- ---------------
Sales.Customers.email       Contact Info    Confidential     Medium
Sales.Customers.ip_address

This command gets the information type and sensitivity label of multiple columns in the database using and array argument containing the column names and the current path context to locate the database.

Columns that have no information type or sensitivity label, such as Sales.Customers.ip_Addess in the example, will return empty results.

Parameters

-ColumnName

Name(s) of columns for which information type and sensitivity label is fetched.

Parameter properties

Type:	String[]
Default value:	None
Supports wildcards:	False
DontShow:	False

Parameter sets

(All)

Position:	Named
Mandatory:	False
Value from pipeline:	False
Value from pipeline by property name:	True
Value from remaining arguments:	False

-ConnectionString

Specifies a connection string to connect to the database. If this parameter is present, other connection parameters will be ignored.

Parameter properties

Type:

String