TechNet
Export (0) Print
Expand All

sys.dm_database_encryption_keys (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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

Column NameData TypeDescription
database_idintID of the database.
encryption_stateintIndicates 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_datedatetimeDisplays the date the encryption key was created.
regenerate_datedatetimeDisplays the date the encryption key was regenerated.
modify_datedatetimeDisplays the date the encryption key was modified.
set_datedatetimeDisplays the date the encryption key was applied to the database.
opened_datedatetimeShows when the database key was last opened.
key_algorithmnvarchar(32)Displays the algorithm that is used for the key.
key_lengthintDisplays the length of the key.
encryptor_thumbprintvarbinary(20)Shows the thumbprint of the encryptor.
encryptor_typenvarchar(32)Applies to: SQL Server (SQL Server 2012 through current version).

Describes the encryptor.
percent_completerealPercent complete of the database encryption state change. This will be 0 if there is no state change.

Requires the VIEW SERVER STATE permission on the server.

Dynamic Management Views and Functions (Transact-SQL)
Security-Related Dynamic Management Views and Functions (Transact-SQL)
Transparent Data Encryption (TDE)
SQL Server Encryption
SQL Server and Database Encryption Keys (Database Engine)
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)

Community Additions

ADD
Show:
© 2016 Microsoft