Set-DbaDbDataClassification

Synopsis

Adds or updates data classification labels on SQL Server table columns

Description

Creates or updates data classification metadata on table columns by setting four extended properties:

• sys_information_type_id: GUID identifying the information type
• sys_information_type_name: Human-readable information type name
• sys_sensitivity_label_id: GUID identifying the sensitivity label
• sys_sensitivity_label_name: Human-readable sensitivity label name

This command performs an upsert: if a classification property already exists on the column it will be
updated, otherwise it will be created. You can update only the information type, only the sensitivity
label, or both at once.

Built-in GUID mappings are provided for well-known Microsoft Information Protection types and labels.
If InformationType or SensitivityLabel matches a known value, the corresponding ID will be set
automatically. Custom values are also supported by providing both the name and ID explicitly.

Known Information Types: Networking, Contact Info, Credentials, Credit Card, Banking, Financial,
Other, Name, National ID, SSN, Health, Date Of Birth

Known Sensitivity Labels: Public, General, Confidential, Confidential - GDPR, Highly Confidential,
Highly Confidential - GDPR

Requires SQL Server 2005 or later due to use of sp_addextendedproperty / sp_updateextendedproperty.

Syntax

Set-DbaDbDataClassification
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-Schema] <String>]
    [[-Table] <String>]
    [[-Column] <String>]
    [[-InformationType] <String>]
    [[-InformationTypeId] <String>]
    [[-SensitivityLabel] <String>]
    [[-SensitivityLabelId] <String>]
    [[-InputObject] <PSObject[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Set-DbaDbDataClassification -SqlInstance sql2019 -Database AdventureWorks -Table Customer -Column EmailAddress -InformationType "Contact Info" -SensitivityLabel "Confidential"

Sets the classification for the EmailAddress column in the Customer table of AdventureWorks.
The InformationTypeId and SensitivityLabelId are automatically populated from the built-in mapping.

Example: 2

PS C:\> Set-DbaDbDataClassification -SqlInstance sql2019 -Database AdventureWorks -Schema HumanResources -Table Employee -Column NationalIDNumber -InformationType "National ID" -SensitivityLabel

“Highly Confidential”
Sets classification for a column in a non-dbo schema.

Example: 3

PS C:\> Get-DbaDbDataClassification -SqlInstance sql2019 -Database AdventureWorks | Set-DbaDbDataClassification -SensitivityLabel "Highly Confidential"

Updates the sensitivity label to “Highly Confidential” for all classified columns in AdventureWorks,
keeping the information type unchanged.

Example: 4

PS C:\> Set-DbaDbDataClassification -SqlInstance sql2019 -Database AdventureWorks -Table Orders -Column CreditCardNumber -InformationType "Credit Card" -InformationTypeId

“D22FA6E9-5EE4-3BDE-4C2B-A409604C4646” -SensitivityLabel “Highly Confidential - GDPR” -Confirm:$false
Sets a classification with an explicit GUID for the information type.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies which database contains the table to classify.

-Schema

The schema of the table to classify. Defaults to “dbo”.

-Table

The table containing the column to classify.

-Column

The column to classify.

-InformationType

The information type name to assign. If this matches a known MIP type, InformationTypeId will be
populated automatically. For custom types, provide InformationTypeId explicitly.

-InformationTypeId

The GUID for the information type. Optional when InformationType matches a known MIP type.

-SensitivityLabel

The sensitivity label name to assign. If this matches a known MIP label, SensitivityLabelId will be
populated automatically. For custom labels, provide SensitivityLabelId explicitly.

-SensitivityLabelId

The GUID for the sensitivity label. Optional when SensitivityLabel matches a known MIP label.

-InputObject

Accepts classification objects piped from Get-DbaDbDataClassification. When used, the Schema, Table,
Column, and database context are taken from the piped object.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Outputs

PSCustomObject

Returns the updated classification object with the same properties as Get-DbaDbDataClassification.