Export (0) Print
Expand All
Expand Minimize

DROP DATABASE ENCRYPTION KEY (Transact-SQL)

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 note 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.

Applies to: SQL Server (SQL Server 2008 through current version), SQL Database V12 (Preview in some regions).

Topic link icon Transact-SQL Syntax Conventions

DROP DATABASE ENCRYPTION KEY

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.

On SQL Server requires CONTROL permission on the database and VIEW DEFINITION permission on the certificate or asymmetric key that is used to encrypt the database encryption key.

On SQL Database only a SQL Database administrator can create a database encryption key.

The following example removes the database encryption and drops the database encryption key.

ALTER DATABASE AdventureWorks2012
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 AdventureWorks2012;
GO
DROP DATABASE ENCRYPTION KEY;
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft