Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

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 Understanding Transparent Data Encryption (TDE).

Topic link iconTransact-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. For more information, see sys.dm_database_encryption_keys (Transact-SQL).

Requires CONTROL permission on the database.

A. Dropping a Database Encryption Key

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

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

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.