By Solomon Rutzky


2016-01-22 19:48:48 8 Comments

I have created a few Certificates (via CREATE CERTIFICATE) and Asymmetric Keys (via CREATE ASYMMETRIC KEY) and used them to sign and counter-sign various Stored Procedures, User-Defined Functions (UDFs), Triggers, and Assemblies (via ADD SIGNATURE). But now I need to find which Certificates and/or Asymmetric Keys have been used to sign which particular modules.

1 comments

@Solomon Rutzky 2016-01-22 19:48:48

The easiest way seems to be using the sys.crypt_properties Catalog View which holds the relationship between Certificates / Asymmetric Keys and signed modules, as well as the usage (i.e. Signature or Counter-Signature). There are a few other means of finding which modules are signed, but none seem to indicate Signature vs Counter-Signature.

SELECT SCHEMA_NAME(so.[schema_id]) AS [SchemaName],
       so.[name] AS [ObjectName],
       so.[type_desc] AS [ObjectType],
       ---
       scp.crypt_type_desc AS [SignatureType],
       ISNULL(sc.[name], sak.[name]) AS [CertOrAsymKeyName],
       ---
       scp.thumbprint
FROM sys.crypt_properties scp
INNER JOIN sys.objects so
        ON so.[object_id] = scp.[major_id]
LEFT JOIN sys.certificates sc
        ON sc.thumbprint = scp.thumbprint
LEFT JOIN sys.asymmetric_keys sak
        ON sak.thumbprint = scp.thumbprint
WHERE   so.[type] <> 'U'
ORDER BY [SchemaName], [ObjectType], [ObjectName], [CertOrAsymKeyName];

In my test DB, this query returns the following:

Schema  ObjectName     ObjectType            SignatureType                        CertOrAsymKeyName  thumbprint
------  ----------     ----------            -------------                        -----------------  ----------
dbo     fnPaymentCalc  SQL_SCALAR_FUNCTION   COUNTER SIGNATURE BY ASYMMETRIC KEY  KeyTest1           0x2333B2FA6AA8004E
dbo     ModuleTest2    SQL_STORED_PROCEDURE  SIGNATURE BY CERTIFICATE             CrossDatabaseCert  0x49BA174584C78C878D923690C15898A809CBACDF
dbo     TestSig        SQL_STORED_PROCEDURE  SIGNATURE BY CERTIFICATE             bob                0x778B3DB4ED981FC27AB301ACE7A1AB8424F64792
dbo     TestSig        SQL_STORED_PROCEDURE  SIGNATURE BY ASYMMETRIC KEY          KeyTest1           0x2333B2FA6AA8004E

Related Questions

Sponsored Content

2 Answered Questions

1 Answered Questions

1 Answered Questions

1 Answered Questions

[SOLVED] Setup SQL Server SSL client certificate

1 Answered Questions

Sponsored Content