Skip to content

drop-column-encryption-key-transact-sql.md

Latest commit

 

History

History
84 lines (61 loc) · 2.93 KB

drop-column-encryption-key-transact-sql.md

File metadata and controls

84 lines (61 loc) · 2.93 KB
title DROP COLUMN ENCRYPTION KEY (Transact-SQL)
description DROP COLUMN ENCRYPTION KEY (Transact-SQL)
author jaszymas
ms.author jaszymas
ms.date 01/30/2026
ms.service sql
ms.subservice t-sql
ms.topic reference
f1_keywords
DROP COLUMN ENCRYPTION
DROP COLUMN ENCRYPTION KEY
DROP_COLUMN_ENCRYPTION_TSQL
DROP_COLUMN_ENCRYPTION_KEY_TSQL
helpviewer_keywords
DROP COLUMN ENCRYPTION KEY statement
column encryption key, drop
dev_langs
TSQL

DROP COLUMN ENCRYPTION KEY (Transact-SQL)

[!INCLUDE sqlserver2016-asdb-asdbmi]

Drops a column encryption key from a database.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

DROP COLUMN ENCRYPTION KEY key_name [;]

Arguments

key_name
The name of the column encryption key to drop from the database.

Remarks

A column encryption key can't be dropped if it's used to encrypt any column in the database. All columns using the column encryption key must first be decrypted or dropped.

To remove encryption from a column:

  1. Decrypt the column - Use ALTER TABLE to modify the encrypted column, removing the encryption specification:

    ALTER TABLE dbo.Employees
ALTER COLUMN SSN NVARCHAR(11);

  2. Drop the column encryption key - After all columns using the key are decrypted, you can drop the key:

    DROP COLUMN ENCRYPTION KEY MyCEK;

Alternatively, if you no longer need the column data, you can drop the column entirely using ALTER TABLE DROP COLUMN before dropping the encryption key.

Permissions

Requires ALTER ANY COLUMN ENCRYPTION KEY permission on the database.

Examples

A. Dropping a column encryption key

The following example drops a column encryption key called MyCEK.

DROP COLUMN ENCRYPTION KEY MyCEK;  
GO

Related content