sys.certificates: How to get Information about all Certificates in a Database

Introduction

When working with certificates, every now and then you need to know which certificates are currently present in the database. Other times you might want to know additional information about a particular certificate, for example when it was last backed up. In those cases you can get answers from the sys.certificates catalog view.

The sys.certificates Catalog View

The sys.certificates catalog view returns one row per certificate in the current database. It has the following columns:

The columns name, certificate_id and sid fall into the category "identifying information". The sid is used when a server principal is created from the certificate. That principal's sid will be set to this value. string_sid is an alternative representation of the same value.

The columns issuer_name, cert_serial_number, subject, expiry_date, start_date and thumbprint are official fields defined in the X.509 certificate standard.

The principal_id column indicates who owns the certificate; and the two columns pvt_key_encryption_type and pvt_key_encryption_type_desc indicate how the private key is protected.

The below image shows a few of the columns of this CV in a database with two certificates:

In addition to the columns described above, there are two more columns that are potentially of interest to you: is_active_for_begin_dialog and pvt_key_last_backup_date. is_active_for_begin_dialog indicates if this certificate can be used to initiate a service broker dialog. pvt_key_last_backup_date on the other hand contains the date and time of the last time the certificate's private key was backed up.

Summary

The sys.certificates catalog view returns one row per certificate in the current database. It returns general information about each certificate that includes properties like the name, the X.509 subject and the last backup date.