Get-SqlSensitivityClassification
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 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByConnectionString
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-Credential
Specifies a credential used to connect to the database.
Parameter properties
| Type: | PSCredential |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByConnectionParameters
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-DatabaseName
Specifies the name of a database. This cmdlet connects to this database in the instance that is specified in the ServerInstance parameter.
If the DatabaseName parameter is not specified, the database that is used depends on whether the current path specifies both the SQLSERVER:\SQL folder and a database name. If the path specifies both the SQL folder and a database name, this cmdlet connects to the database that is specified in the path.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByConnectionParameters
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-InputObject
Specifies a SQL Server Management Object (SMO) that represent the database that this cmdlet uses.
Parameter properties
| Type: | Database |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByDBObject
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | True |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-Path
Specifies the path to the instance of SQL Server on which this cmdlet runs the operation. If you do not specify a value for this parameter, the cmdlet uses the current working location.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByPath
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-ServerInstance
Specifies either the name of the server instance (a string) or SQL Server Management Objects (SMO) object that specifies the name of an instance of the Database Engine. For default instances, only specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName.
Parameter properties
| Type: | PSObject |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByConnectionParameters
| Position: | Named |
| Mandatory: | True |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-SuppressProviderContextWarning
Indicates that this cmdlet suppresses the warning that this cmdlet has used in the database context from the current SQLSERVER:\SQL path setting to establish the database context for the cmdlet.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByContext
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
CommonParameters
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutBuffer, -OutVariable, -PipelineVariable, -ProgressAction, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.