查詢以列出數據庫的加密證書

使用什麼證書來加密實例上的每個數據庫。

我可以使用以下方法獲取數據，但如何編寫查詢

USE master
GO

-- this provides the list of certificates
SELECT * FROM sys.certificates


-- this provides the list of databases (encryption_state = 3) is encrypted
SELECT * FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;

我注意到 sys.certifcates.thumbprint 和 sys.dm_database_encryption_keys.encryptor_thumbprint 列包含相同的數據。

您可以加入證書指紋：

use master;
go

select
   database_name = d.name,
   dek.encryptor_type,
   cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;

我的範例輸出：

database_name           encryptor_type    cert_name
=============           ==============    =========
tempdb                  ASYMMETRIC KEY    NULL
AdventureWorks2012TDE   CERTIFICATE       TdeCert

對於顯示哪些數據庫已加密或未加密的更深入的查詢，它們的證書以及重要的是加密設置是否已實際完成。加密有時可能需要很長時間才能完成或卡住。

SELECT D.name AS 'Database Name'
,c.name AS 'Cert Name'
,E.encryptor_type AS 'Type'
,case
   when E.encryption_state = 3 then 'Encrypted'
   when E.encryption_state = 2 then 'In Progress'
   else 'Not Encrypted'
end as state,
E.encryption_state, E.percent_complete, E.key_algorithm, E.key_length, E.* FROM sys.dm_database_encryption_keys E
right join sys.databases D on D.database_id = E.database_id
left join sys.certificates c ON E.encryptor_thumbprint=c.thumbprint

引用自：https://dba.stackexchange.com/questions/92080