Locating Signed Procedures in Sql Server
I recently had to redeploy a DB on another server with a whole new master key. This meant that I had to destroy and recreate all the existing Certificates and Keys that I’d created based off the previous master key.
The biggest problem for me was locating all the pesky procedures that I’d signed previously that would need to be resigned with a new certificate; however locating this took a bit of time. Eventually I was able to come up with the following query:
Select Schema_Name(O.schema_id) + '.' + Object_Name(Cp.major_id) As 'Module' ,cp.crypt_type_desc As 'Method' ,Coalesce(C.name, AK.name) As 'Signing Object'From sys.crypt_properties As Cp -- Get the schema Inner Join sys.objects As O On O.object_id = Cp.major_id -- Asymmetric Key (if any) Left Join sys.asymmetric_keys As Ak On Ak.thumbprint = Cp.thumbprint -- Ceritificate (if any) Left Join sys.certificates As C On C.thumbprint = Cp.thumbprint
|
Advertisement
Categories: Uncategorized
