Securing your data is very important and database encryption is just part of that landscape. Taking advantage SQL Server database encryption can be a daunting task but once it is configured it is also important to monitor it.
This query is intended to allow the database administrator to gain information about the status of encryption on their systems. This script can be very handy for audits or to ensure that your databases are in an a state you expect them to be in.
For those databases in your environment that require Transparent Data Encryption (TDE) this script will be invaluable for monitoring the encryption states of your databases.
SELECT
[db].name,
[db].is_encrypted,
[dek].encryption_state,
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 'Desc'
FROM
sys.dm_database_encryption_keys [dek]
RIGHT JOIN
sys.databases [db] ON [dek].database_id = [db].database_id