Find out what stored procedures are signed using certificate in SQL Server 2008

Question

I am producing a database update script I want to find out stored procedures have been signed using various certificates.

I can get the list of stored procedures using sys.procedures and I can get the list of certificates using sys.certificates but I cannot find out what stored procedures are signed using the various certificates

Is there a sys.procedures_certificates view or something like that?? Maybe there is a way on the SQL Server Management Studio GUI that tells me this..

I have spent quite some time googling this but to no avail.

Thanks in advance for your help..

Answer 1

You should be able to use sys.crypt_properties to get this quite easily - check out the msdn article here

Answer 2

SELECT [Object Name] = object_name(cp.major_id),
       [Object Type] = obj.type_desc,   
       [Cert/Key] = coalesce(c.name, a.name),
       cp.crypt_type_desc
FROM   sys.crypt_properties cp
INNER JOIN sys.objects obj        ON obj.object_id = cp.major_id
LEFT   JOIN sys.certificates c    ON c.thumbprint = cp.thumbprint
LEFT   JOIN sys.asymmetric_keys a ON a.thumbprint = cp.thumbprint
ORDER BY [Object Name] ASC