Returns information about the encryption state of a database and its associated database encryption keys.
T-SQL:
SELECT
UPPER([db].Name) as 'Database Name',
[db].Database_ID,
[db].User_Access_Desc as 'Database User Access Mode',
[db].State_Desc as 'Database State',
[db].Is_Encrypted,
CASE [dek].Encryption_State
WHEN 0 THEN 'Not Encrypted'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
WHEN 6 THEN 'Protection change in progress '
ELSE 'Not Encrypted'
END AS 'Encryption_State',
[dek].create_date as 'Encryption Create Date',
[dek].key_algorithm as 'Encryption Key Algorithm',
[dek].key_length as 'Encryption Key Length'
FROM
sys.dm_database_encryption_keys [dek]
Right Join
sys.databases [db] ON [dek].database_id = [db].database_id
order by 1 ASC
|
|
|
|
|
|