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.