Geek Answers Handbook

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

I am creating a script database update. I want to know that the stored procedures were signed using various certificates.

I can get a list of stored procedures using sys.procedures, and I can get a list of certificates using sys.certificates, but I can’t find out which stored procedures are signed using different certificates

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

I spent quite a bit of time searching on Google, but to no avail.

Thanks in advance for your help.

+4
source share
2 answers

You can use sys.crypt_properties to get this pretty easy - see the msdn article here

+6
source 
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
+4
source

More articles:


All Articles