helpful:SQL Server 2005 helpful catalogs: crypt_properties and key_encryptions

不久之前，我回答过一个问题：如找出被证书(certificate)加密的密钥。 我写了一些查询，用于找出那些加密,签名的信息。在展示这些查询之前，我会对这两个视图做个简要的介绍：
crypt_properties视图保存了签名（signature）相关的信息。可以用其中的thumbprint列来确定相关的密钥。
key_encryptions视图保存了对称密钥加密（symmetric key）的相关信息。和crypt_properties视图一样，非对称密钥（asymmetric key）和证书也是用thumbprint列来确定的。 但是，对称密钥则完全不同，需要用到GUID.


现在列出这些查询 ：



--被证书cert1签名的对象
select object_name(cp.major_id) from sys.crypt_properties cp, sys.certificates c where cp.thumbprint = c.thumbprint and c.name = 'cert1';

--查找被非对称密钥akey1签名的对象
select object_name(cp.major_id) from sys.crypt_properties cp, sys.asymmetric_keys ak where cp.thumbprint = ak.thumbprint and ak.name = 'akey1';

-- 被证书cert1加密的对称密钥
select sk.name from sys.key_encryptions ke, sys.certificates c, sys.symmetric_keys sk where ke.thumbprint = c.thumbprint and c.name = 'cert1' and ke.key_id = sk.symmetric_key_id
--被非对称密钥akey1加密的对称密钥
select sk.name from sys.key_encryptions ke, sys.asymmetric_keys ak, sys.symmetric_keys sk where ke.thumbprint = ak.thumbprint and ak.name = 'akey1' and ke.key_id = sk.symmetric_key_id

--被对称密钥skey1加密的对称密钥
select sk1.name from sys.key_encryptions ke, sys.symmetric_keys sk1, sys.symmetric_keys sk2 where ke.thumbprint = sk2.key_guid and sk2.name = 'skey1' and ke.key_id = sk1.symmetric_key_id
-- 查找加密了对称密钥skey1的证书
select c.name from sys.key_encryptions ke, sys.certificates c, sys.symmetric_keys sk where ke.thumbprint = c.thumbprint and sk.name = 'skey1' and ke.key_id = sk.symmetric_key_id

-- 加密了对称密钥skey1的非对称密钥
select ak.name from sys.key_encryptions ke, sys.asymmetric_keys ak, sys.symmetric_keys sk where ke.thumbprint = ak.thumbprint and sk.name = 'skey1' and ke.key_id = sk.symmetric_key_id

--加密了对称密钥skey1的对称密钥
select sk2.name from sys.key_encryptions ke, sys.symmetric_keys sk1, sys.symmetric_keys sk2 where ke.thumbprint = sk2.key_guid and sk1.name = 'skey1' and ke.key_id = sk1.symmetric_key_id

延伸阅读