錯誤：“無法刪除證書，因為一個或多個實體已使用它進行簽名或加密。”

我有一個要從數據庫中刪除的證書。

如果我發出命令

DROP CERTIFICATE <FooCert>

我得到錯誤

The certificate cannot be dropped because one or more entities are either signed or encrypted using it

根據 Jason Strate 的說法，我應該能夠找出證書籤名的內容。

以下查詢返回 0 行：

SELECT OBJECT_SCHEMA_NAME(co.major_id) + '.' + OBJECT_NAME(co.major_id)
FROM sys.certificates c 
INNER JOIN sys.crypt_properties co ON c.thumbprint = co.thumbprint
WHERE co.crypt_type_desc = 'SIGNATURE BY CERTIFICATE' 
AND c.name = 'FooCert'

我還嘗試根據這個 SO question 將實體解耦。 https://stackoverflow.com/questions/52460/how-do-i-find-and-decouple-entities-from-a-certificate-when-upgrading-ms-sqlserv

如何刪除此證書的依賴項以便刪除它？

要查找與證書和非對稱密鑰相關的項目，請首先嘗試在此 DBA.SE 答案中發布的查詢：

查找簽名的過程、函式、觸發器、程序集以及哪些證書/非對稱密鑰

如果這不返回任何對象，請嘗試以下查詢：

• 登錄
• 使用者
• 服務代理端點
• 數據庫鏡像端點
• 對稱鍵
• 數據庫加密密鑰（用於 TDE）

請注意，登錄是伺服器/實例級別，而其他一切都是數據庫級別。此外，數據庫加密密鑰雖然處於數據庫級別，但在 DMV 中報告，該 DMV 返回所有數據庫的數據，因此不會根據“目前”數據庫而更改。

-- Server / Instance Logins (results not sensitive to local / current Database)
;WITH certs_n_keys AS
(
 SELECT 'Certifcate' AS [Type], crts.name, crts.certificate_id AS [cert_or_asymkey_id],
        crts.principal_id, crts.pvt_key_encryption_type_desc, crts.[sid],
        crts.thumbprint
 FROM   [master].sys.certificates crts
 UNION ALL
 SELECT 'Asymmetric Key' AS [Type], asym.name, asym.asymmetric_key_id AS
        [cert_or_asymkey_id], asym.principal_id, asym.pvt_key_encryption_type_desc,
        asym.[sid], asym.thumbprint
 FROM   [master].sys.asymmetric_keys asym
)
SELECT cnk.*, '---' AS [---],
      sp.[name] AS [PrincipalName], sp.principal_id, sp.type_desc,
      sp.create_date, sp.modify_date
FROM   certs_n_keys cnk
INNER JOIN sys.server_principals sp
       ON sp.[sid] = cnk.[sid];


-- Database Users
;WITH certs_n_keys AS
(
 SELECT 'Certifcate' AS [Type], crts.name, crts.certificate_id AS [cert_or_asymkey_id],
        crts.principal_id, crts.pvt_key_encryption_type_desc, crts.[sid],
        crts.thumbprint
 FROM   sys.certificates crts
 UNION ALL
 SELECT 'Asymmetric Key' AS [Type], asym.name, asym.asymmetric_key_id AS
        [cert_or_asymkey_id], asym.principal_id, asym.pvt_key_encryption_type_desc,
        asym.[sid], asym.thumbprint
 FROM   sys.asymmetric_keys asym
)
SELECT cnk.*, '---' AS [---],
      dp.[name] AS [PrincipalName], dp.principal_id, dp.type_desc,
      dp.create_date, dp.modify_date
FROM   certs_n_keys cnk
INNER JOIN sys.database_principals dp
       ON dp.[sid] = cnk.[sid];


-- Service Broker Endpoints
SELECT crts.name, crts.certificate_id, crts.principal_id,
      crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
      endpts.*
FROM   sys.certificates crts
INNER JOIN sys.service_broker_endpoints endpts
       ON endpts.certificate_id = crts.certificate_id;


-- Database Mirroring Endpoints
SELECT crts.name, crts.certificate_id, crts.principal_id,
      crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
      endpts.*
FROM   sys.certificates crts
INNER JOIN sys.database_mirroring_endpoints endpts
       ON endpts.certificate_id = crts.certificate_id;


-- Symmetric Keys (scroll results to the right to see Key name)
SELECT crts.name, crts.certificate_id, crts.principal_id,
      crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
      ncrptns.*, '---' AS [---], symkys.*
FROM   sys.certificates crts
INNER JOIN sys.key_encryptions ncrptns
       ON ncrptns.[thumbprint] = crts.[thumbprint]
INNER JOIN sys.symmetric_keys symkys
       ON symkys.[symmetric_key_id] = ncrptns.[key_id];


-- Database Encryption Keys (for TDE; results not sensitive to local / current Database)
SELECT crts.name, crts.certificate_id, crts.principal_id,
      crts.pvt_key_encryption_type_desc, crts.[sid], crts.thumbprint, '---' AS [---],
      DB_NAME(dbkeys.[database_id]) AS [DatabaseName], dbkeys.*
FROM   [master].sys.certificates crts
INNER JOIN sys.dm_database_encryption_keys dbkeys
       ON dbkeys.[encryptor_thumbprint] = crts.[thumbprint];

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