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 SQL modules:

  • Stored Procedures
  • User-Defined Functions (UDFs)
  • Triggers
  • Assemblies

Signing was done via ADD SIGNATURE.

But now I need to find which Certificates and/or Asymmetric Keys have been used to sign which particular modules.

1 Answer 1


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],
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.