不久之前，我回答过一个问题：如找出被证书(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
原文地址：SQL Server 2005 helpful catalogs: crypt_properties and key_encryptions
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
