title: "sys.dm_database_encryption_keys (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/20/2017" ms.prod: "sql" ms.prod_service: "database-engine, sql-database" ms.service: "" ms.component: "dmv's" ms.reviewer: "" ms.suite: "sql" ms.technology:
- "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:
- "sys.dm_database_encryption_keys"
- "sys.dm_database_encryption_keys_TSQL"
- "dm_database_encryption_keys"
- "dm_database_encryption_keys_TSQL" dev_langs:
- "TSQL" helpviewer_keywords:
- "sys.dm_database_encryption_keys dynamic management view" ms.assetid: 56fee8f3-06eb-4fff-969e-abeaa0c4b8e4 caps.latest.revision: 25 author: "stevestein" ms.author: "sstein" manager: "craigg" ms.workload: "On Demand" monikerRange: "= azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions"
[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]
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 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 | nvarchar(32) | Displays the algorithm that is used for the key. |
| key_length | int | Displays the length of the key. |
| encryptor_thumbprint | varbinary(20) | Shows the thumbprint of the encryptor. |
| encryptor_type | nvarchar(32) | Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL11] through current version). Describes the encryptor. |
| percent_complete | real | Percent complete of the database encryption state change. This will be 0 if there is no state change. |
On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On [!INCLUDEssSDS_md], requires the VIEW DATABASE STATE permission in the database.
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)