Assymmetric Encryption – Useful DMVs

–Retrieving a list of certificates installed in the current database
SELECT
name,
certificate_id,
pvt_key_encryption_type_desc,
subject,
cert_serial_number,
start_date,
expiry_date,
thumbprint
FROM sys.certificates;

–Listing encrypted databases
SELECT
DB_NAME(database_id) AS database_name,
database_id,
CASE encryption_state
WHEN 0 THEN N’No database encryption key present, no encryption’
WHEN 1 THEN N’Unencrypted’
WHEN 2 THEN N’Encryption in progress’
WHEN 3 THEN N’Encrypted’
WHEN 4 THEN N’Key change in progress’
WHEN 5 THEN N’Decryption in progress’
END AS encryption_state,
key_algorithm,
key_length,
percent_complete
FROM sys.dm_database_encryption_keys;