Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Get TDE (Transparent Data Encryption) Info - along with encryption state, certificate, etc
/*************************************************************************************************************************************************
Author : ██╗ ██╗██╗███╗ ██╗ ███████╗██╗ ██╗ █████╗ ██╗ ██╗
██║ ██╔╝██║████╗ ██║ ██╔════╝██║ ██║██╔══██╗██║ ██║
█████╔╝ ██║██╔██╗ ██║ ███████╗███████║███████║███████║
██╔═██╗ ██║██║╚██╗██║ ╚════██║██╔══██║██╔══██║██╔══██║
██║ ██╗██║██║ ╚████║ ███████║██║ ██║██║ ██║██║ ██║
╚═╝ ╚═╝╚═╝╚═╝ ╚═══╝ ╚══════╝╚═╝ ╚═╝╚═╝ ╚═╝╚═╝ ╚═╝ (@TheRockStarDBA)
Sr. DBA
Date : - Original Version created on 01.15.2016
Comments : - This script list out the databases, encryption status along with other useful information.
- Use this script if you have TDE enabled in your environment.
Usage : - You can use this script free by keeping this header as is and give due credit to the author of this script which is ME :-)
- Use it as per your risk --> Neither Me or my employer is responsible for any
- DATA LOSS !!
- Financial LOSS !!
- Emotional LOSS !!
- ANY LOSS THAT YOU CAN THINK !!
**************************************************************************************************************************************************/
set nocount on
if exists (select 1 from master.sys.databases where is_encrypted = 1)
begin
select DB_NAME(db.database_id) as DatabaseName
,c.name as CertificateName
,case
when dek.encryption_state = 0
then 'No database encryption key present, no encryption'
when dek.encryption_state = 1
then 'Unencrypted'
when dek.encryption_state = 2
then 'Encryption in progress'
when dek.encryption_state = 3
then 'Encrypted'
when dek.encryption_state = 4
then 'Key change in progress'
when dek.encryption_state = 5
then 'Decryption in progress'
when dek.encryption_state = 6
then 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
end as encryption_state_desc
,dek.create_date
,regenerate_date
,modify_date
,set_date
,opened_date
,key_algorithm
,key_length
,encryptor_thumbprint
,case
when dek.percent_complete = 0
then 'no state change'
else cast(dek.percent_complete as varchar(5))
end as percent_complete
,[certificate_id]
,[principal_id]
,[pvt_key_encryption_type]
,[pvt_key_encryption_type_desc]
,[issuer_name]
,[cert_serial_number]
,[subject]
,[expiry_date]
,[start_date]
,[thumbprint]
,[pvt_key_last_backup_date]
from master.sys.dm_database_encryption_keys as dek
left join master.sys.certificates as c on dek.encryptor_thumbprint = c.thumbprint
inner join master.sys.databases as db on dek.database_id = db.database_id
order by DB_NAME(db.database_id)
end
else
select 'There is no TDE encryption configured on '+@@servername + ' !!' as Information
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.