Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



T-SQL Encryption Status of a database
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-08-14








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