--- title: "Set-DbaDbDataClassification" description: "Adds or updates data classification labels on SQL Server table columns" url: "https://dbatools.io/Set-DbaDbDataClassification/" availability: "Windows, Linux, macOS" tags: ["DataClassification", "Classification", "Compliance", "Security"] author: "the dbatools team + Claude" source: "https://github.com/dataplat/dbatools/blob/master/public/Set-DbaDbDataClassification.ps1" last_updated: "2024-01-01" --- # 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 ```powershell Set-DbaDbDataClassification [[-SqlInstance] ] [[-SqlCredential] ] [[-Database] ] [[-Schema] ] [[-Table] ] [[-Column] ] [[-InformationType] ] [[-InformationTypeId] ] [[-SensitivityLabel] ] [[-SensitivityLabelId] ] [[-InputObject] ] [-EnableException] [-WhatIf] [-Confirm] [] ``` ## Examples ### Example 1: Sets the classification for the EmailAddress column in the Customer table of AdventureWorks ```powershell 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: "Highly Confidential" Sets classification for a column in a non-dbo schema ```powershell PS C:\> Set-DbaDbDataClassification -SqlInstance sql2019 -Database AdventureWorks -Schema HumanResources -Table Employee -Column NationalIDNumber -InformationType "National ID" -SensitivityLabel ``` ### Example 3: Updates the sensitivity label to "Highly Confidential" for all classified columns in AdventureWorks, keeping... ```powershell 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: "D22FA6E9-5EE4-3BDE-4C2B-A409604C4646" -SensitivityLabel "Highly Confidential - GDPR" -Confirm:$false Sets a... ```powershell 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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -Database Specifies which database contains the table to classify. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -Schema The schema of the table to classify. Defaults to "dbo". | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | dbo | ##### -Table The table containing the column to classify. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -Column The column to classify. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -InformationTypeId The GUID for the information type. Optional when InformationType matches a known MIP type. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -SensitivityLabelId The GUID for the sensitivity label. Optional when SensitivityLabel matches a known MIP label. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -InputObject Accepts classification objects piped from Get-DbaDbDataClassification. When used, the Schema, Table, Column, and database context are taken from the piped object. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | true (ByValue) | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ##### -WhatIf Shows what would happen if the command were to run. No actions are actually performed. | Property | Value | | --- | --- | | Alias | wi | | Required | False | | Pipeline | false | | Default Value | | ##### -Confirm Prompts you for confirmation before executing any changing operations within the command. | Property | Value | | --- | --- | | Alias | cf | | Required | False | | Pipeline | false | | Default Value | | ## Outputs **PSCustomObject** Returns the updated classification object with the same properties as Get-DbaDbDataClassification. --- Part of [dbatools](https://dbatools.io/), a free and open source PowerShell module for SQL Server administration. Full command index: https://dbatools.io/commands/