MSSQL Database 암호화 키 삭제
DB Skill/MSSQL 2021. 6. 6. 07:17
-- 데이터 베이스 암호화 제거
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION OFF;
-- 암호화 키 삭제
DROP DATABASE ENCRYPTION KEY
-- 데이터베이스 암호화 확인
SELECT db_name(database_id), encryption_state
FROM sys.dm_database_encryption_keys;
---------------------------
---------------------------
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
---------------------
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
참고 자료
'DB Skill > MSSQL' 카테고리의 다른 글
MSSQL 서비스 도중 Hostname 변경 (0) | 2021.06.06 |
---|---|
MSSQL Long Running Job Check (0) | 2021.06.06 |
MSSQL CPU 선호도 설정 (0) | 2021.06.06 |
MSSQL Table Constraints Check 비활성화 (0) | 2021.06.06 |
MSSQL backup history delete (0) | 2021.06.06 |