sys.dm_database_encryption_keys (Transact-SQL)

Returns information about the encryption state of a database and its associated database encryption keys. For more information about database encryption, see Understanding Transparent Data Encryption (TDE).

Column Name	Data Type	Description
database_id	int	ID of the database.
encryption_state	int	Indicates whether the database is encrypted or not encrypted. 0 = No database encryption key present, no encryption 1 = Unencrypted 2 = Encryption in progress 3 = Encrypted 4 = Key change in progress 5 = Decryption in progress 6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)
create_date	datetime	Displays the date the encryption key was created.
regenerate_date	datetime	Displays the date the encryption key was regenerated.
modify_date	datetime	Displays the date the encryption key was modified.
set_date	datetime	Displays the date the encryption key was applied to the database.
opened_date	datetime	Shows when the database key was last opened.
key_algorithm	varchar(?)	Displays the algorithm that is used for the key.
key_length	int	Displays the length of the key.
encryptor_thumbprint	varbin	Shows the thumbprint of the encryptor.
percent_complete	real	Percent complete of the database encryption state change. This will be 0 if there is no state change.

Permissions

Requires the VIEW SERVER STATE permission on the server.

See Also

Reference

Dynamic Management Views and Functions (Transact-SQL)

Security-Related Dynamic Management Views and Functions (Transact-SQL)

Encryption Hierarchy

ALTER DATABASE SET Options (Transact-SQL)

CREATE DATABASE ENCRYPTION KEY (Transact-SQL)

ALTER DATABASE ENCRYPTION KEY (Transact-SQL)

DROP DATABASE ENCRYPTION KEY (Transact-SQL)

Concepts

Understanding Transparent Data Encryption (TDE)

SQL Server Encryption

SQL Server and Database Encryption Keys (Database Engine)

Change History

Updated content
Added the description for encryption_state 6.