Certificate dependency

Knowing whether a certificate exists and/or is in use by a dependent object is good, knowing how it's used is slightly better, but knowing exactly which objects are using a certificate is what is really useful.

This post is a follow-up to a previous post concerning the use of certificates, so without further ado...

...here's the function:

create function dbo.fnList_Certificate_Dependencies
 (
 @certificateName sysname  = null
 )
returns table
as
return (
 select sys.certificates.[name] as CertificateName
  ,sys.certificates.certificate_id as CertificateId
  ,'database principal' as DependentObjectType
  ,sys.database_principals.[name] as DependentObjectName
  ,sys.database_principals.principal_id as DependentObjectId
  from sys.certificates
   inner join sys.database_principals
     on sys.database_principals.sid = sys.certificates.sid
  where ((sys.certificates.[name] = @certificateName)
    or (@certificateName is null))
 union
 select sys.certificates.[name]
  ,sys.certificates.certificate_id
  ,'symmetric key'
  ,sys.symmetric_keys.[name]
  ,sys.symmetric_keys.symmetric_key_id
  from sys.certificates
   inner join sys.key_encryptions
     on sys.key_encryptions.thumbprint = sys.certificates.thumbprint
     inner join sys.symmetric_keys
       on sys.symmetric_keys.symmetric_key_id = sys.key_encryptions.key_id
  where ((sys.certificates.[name] = @certificateName)
    or (@certificateName is null))
 union
 select sys.certificates.[name]
  ,sys.certificates.certificate_id
  ,coalesce(lower(replace(sys.objects.type_desc, '_', ' ')), 'unknown')
  ,coalesce(sys.objects.[name], 'unknown')
  ,sys.objects.[object_id]
  from sys.certificates
   inner join sys.crypt_properties
     on sys.crypt_properties.thumbprint = sys.certificates.thumbprint
     left join sys.objects
       on sys.objects.[object_id] = sys.crypt_properties.major_id
  where ((sys.certificates.[name] = @certificateName)
    or (@certificateName is null))
 )
go

The function lists the name and the id of the certificate in question along with the names, ids and a description of the dependent object's type. Possible dependent objects are discussed in this post.

Two examples of use:

• to list dependencies of a specific certificate (e.g. 'SalesProxy'):

  select *
   from dbo.fnList_Certificate_Dependencies('SalesProxy')

• to list dependencies of all certificates:

  select *
   from dbo.fnList_Certificate_Dependencies(null)

Warning!
Note that neither of the functions posted here detect whether a certificate has been used to encrypt data, so backup your certificates before dropping them and unintentionally encrypting your data permanently!

ML