Archive

Archive for January 28, 2009

Locating Signed Procedures in Sql Server

January 28, 2009 Leave a comment

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

 
 

Categories: Uncategorized
Follow

Get every new post delivered to your Inbox.