7

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..

Michael Duffy
  • 97
  • 2
  • 6

2 Answers2

9
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
Fza
  • 223
  • 2
  • 5
  • Please provide some explanation in a few words whenever possible. – Stacked Jun 21 '16 at 12:23
  • Submitted an edit to clarify. Also, if you specifically wanted **just** stored-procs, you could substitute `sys.procedures` for `sys.objects`, and/or filter by `obj.type` or `obj.type_desc` (type description). E.g. `WHERE obj.type_desc = 'SQL_STORED_PROCEDURE'`. – NateJ Mar 29 '18 at 18:34
8

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

Johnv2020
  • 2,088
  • 3
  • 20
  • 36
  • 9
    Thanks very much for that. It is much appreciated. Here is a little query that may be of help to people. It shows the stored procedures signed by a certificate and the certificate they are signed with. select cer.name AS [Certificate], pro.name AS [Stored Procedure] from sys.procedures pro inner join sys.crypt_properties cry on cry.major_id = pro.object_id inner join sys.certificates cer on cer.thumbprint = cry.thumbprint order by cer.name – Michael Duffy Apr 25 '13 at 10:02
  • @MichaelDuffy your comment is a superb example of quality-add; it extends the answer with the perfect bit of info (the relationship between the tables, i.e. columns on which to JOIN) to make it fully & immediately useful. Nicely done! – NateJ Mar 29 '18 at 18:36