The sys.symmetric_keys Catalog View

Introduction

Similar to the sys.asymmetric_keys catalog view that allows us to inspect the properties of existing asymmetric keys, the sys.symmetric_keys catalog view can be used to inquire about existing symmetric keys.

A sys.symmetric_keys Example

The sys.symmetric_keys catalog view returns a single row for each symmetric key in the current database. The following statement shows the columns available:

The name column returns the name of the key. The principal_id specifies the database principal that owns the key. The symmetric_key_id is an integer identifier for the key. It is unique within a single database.

key_length, key_algorithm and algorithm_desc are the cryptographic properties of the key. They describe the algorithm that the key can be used with, as well as the number of bits that make up the key.

The create_date column lets us know when the key was created in the current database. The modify_date column on the other hand informs us when the ALTER SYMMETRIC KEY statement was last used on the key.

The key_guid column is a globally unique identifier for the key. This guid is part of the encrypted value and is used by the DECRYPTBYKEY function to identify the correct key to use.

The remaining values, key_thumbprint, provider_type, cryptographic_provider_guid and cryptographic_provider_algid are used for keys provided by an EKM module. For SQL Server generated keys, all four columns return NULL.

The output of the above query is shown in the image below.

Summary

The sys.symmetric_keys catalog view returns the properties of all symmetric keys in the current database.