Note
Access to this page requires authorization. You can try signing in or .
Access to this page requires authorization. You can try .
DROP DATABASE ENCRYPTION KEY (Transact-SQL)
Applies to: 👁 Image
SQL Server 👁 Image
Azure SQL Managed Instance 👁 Image
Azure Synapse Analytics 👁 Image
Analytics Platform System (PDW)
Drops a database encryption key that is used in transparent database encryption. For more information about transparent database encryption, see Transparent Data Encryption (TDE).
Important
The backup of the certificate that was protecting the database encryption key should be retained even if the encryption is no longer enabled on a database. Even though the database is not encrypted anymore, parts of the transaction log may still remain protected, and the certificate may be needed for some operations until the full backup of the database is performed.
👁 Image
Transact-SQL syntax conventions
Syntax
DROP DATABASE ENCRYPTION KEY
Note
This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.
Remarks
If the database is encrypted, you must first remove encryption from the database by using the ALTER DATABASE statement. Wait for decryption to complete before removing the database encryption key. For more information about the ALTER DATABASE statement, see ALTER DATABASE SET Options (Transact-SQL). To view the state of the database, use the sys.dm_database_encryption_keys dynamic management view.
Permissions
Requires CONTROL permission on the database.
Examples
The following example removes the database encryption and drops the database encryption key.
ALTER DATABASE AdventureWorks2022
SET ENCRYPTION OFF;
GO
/* Wait for decryption operation to complete, look for a
value of 1 in the query below. */
SELECT encryption_state
FROM sys.dm_database_encryption_keys;
GO
USE AdventureWorks2022;
GO
DROP DATABASE ENCRYPTION KEY;
GO
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
The following example removes the TDE encryption and then drops the database encryption key.
ALTER DATABASE AdventureWorksPDW2012
SET ENCRYPTION OFF;
GO
/* Wait for decryption operation to complete, look for a
value of 1 in the query below. */
WITH dek_encryption_state AS
(
SELECT ISNULL(db_map.database_id, dek.database_id) AS database_id, encryption_state
FROM sys.dm_pdw_nodes_database_encryption_keys AS dek
INNER JOIN sys.pdw_nodes_pdw_physical_databases AS node_db_map
ON dek.database_id = node_db_map.database_id AND dek.pdw_node_id = node_db_map.pdw_node_id
LEFT JOIN sys.pdw_database_mappings AS db_map
ON node_db_map .physical_name = db_map.physical_name
INNER JOIN sys.dm_pdw_nodes AS nodes
ON nodes.pdw_node_id = dek.pdw_node_id
WHERE dek.encryptor_thumbprint <> 0x
)
SELECT TOP 1 encryption_state
FROM dek_encryption_state
WHERE dek_encryption_state.database_id = DB_ID('AdventureWorksPDW2012 ')
ORDER BY (CASE encryption_state WHEN 3 THEN -1 ELSE encryption_state END) DESC;
GO
USE AdventureWorksPDW2012;
GO
DROP DATABASE ENCRYPTION KEY;
GO
See Also
Transparent Data Encryption (TDE)
SQL Server Encryption
SQL Server and Database Encryption Keys (Database Engine)
Encryption Hierarchy
ALTER DATABASE SET Options (Transact-SQL)
CREATE DATABASE ENCRYPTION KEY (Transact-SQL)
ALTER DATABASE ENCRYPTION KEY (Transact-SQL)
sys.dm_database_encryption_keys (Transact-SQL)
Feedback
Was this page helpful?