DROP DATABASE ENCRYPTION KEY (Transact-SQL)DROP DATABASE ENCRYPTION KEY (Transact-SQL)

适用对象: 是SQL Server 否Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

删除透明数据库加密使用的数据库加密密钥。Drops a database encryption key that is used in transparent database encryption. 有关透明数据库加密的详细信息,请参阅透明数据加密 (TDE)For more information about transparent database encryption, see Transparent Data Encryption (TDE).

重要

即使对于数据库不再启用加密,用于保护数据库加密密钥的证书备份也应保留。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.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

DROP DATABASE ENCRYPTION KEY  

RemarksRemarks

如果数据库已加密,则必须先使用 ALTER DATABASE 语句对数据库进行解密。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. 有关 ALTER DATABASE 语句的详细信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For more information about the ALTER DATABASE statement, see ALTER DATABASE SET Options (Transact-SQL). 若要查看数据库的状态,请使用 sys.dm_database_encryption_keys 动态管理视图。To view the state of the database, use the sys.dm_database_encryption_keys dynamic management view.

权限Permissions

要求对数据库具有 CONTROL 权限。Requires CONTROL permission on the database.

示例Examples

下面的示例对数据库进行解密并删除该数据库的加密密钥。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  

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

以下示例对 TDE 进行解密和然后删除该数据库的加密密钥。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

透明数据加密 (TDE) Transparent Data Encryption (TDE)
SQL Server 加密 SQL Server Encryption
SQL Server 和数据库加密密钥(数据库引擎) SQL Server and Database Encryption Keys (Database Engine)
加密层次结构 Encryption Hierarchy
ALTER DATABASE SET 选项 (Transact-SQL) ALTER DATABASE SET Options (Transact-SQL)
CREATE DATABASE ENCRYPTION KEY (Transact-SQL) CREATE DATABASE ENCRYPTION KEY (Transact-SQL)
ALTER DATABASE ENCRYPTION KEY (Transact-SQL) ALTER DATABASE ENCRYPTION KEY (Transact-SQL)
sys.dm_database_encryption_keys (Transact-SQL)sys.dm_database_encryption_keys (Transact-SQL)