Home > Uncategorized > Locating Signed Procedures in Sql Server

Locating Signed Procedures in Sql Server

January 28, 2009 Leave a comment Go to comments

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
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

Follow

Get every new post delivered to your Inbox.