The sp_encryption system procedure reports a new key type called "database encryption key" to show if a database is fully encrypted.
1> create encryption key key1 as default for database encryption 2> go 1> sp_encryption helpkey, key1 Key Name Key Owner Key Length Key Algorithm Key Type Pad Initialization Vector Protected By Key Recovery # of Key Copies ----------- ------------ ------------ ------------- -------------------------------- ------ --------------------- -------------- ---------------- ----------------- key1 dbo 256 AES symmetric database encryption key 0 1 master key 0 0 1> create encryption key key2 for database encryption with master key 2> create encryption key key3 for database encryption with dual_control 3> go 1> sp_encryption helpkey, 'key%' Key Name Key Owner Key Length Key Algorithm Key Type Pad Initialization Vector Protected By Key Recovery # of Key Copies ----------- ------------ ------------ ------------- -------------------------------- ------ --------------------- -------------- ---------------- ----------------- key1 dbo 256 AES symmetric database encryption key 0 1 master key 0 0 key2 dbo 256 AES symmetric database encryption key 0 1 master key 0 0 key3 dbo 256 AES symmetric database encryption key 0 1 dual_control(master key + dual master key) 0 0 1> create database encr_db1 encrypt with key1 2> create database encr_db2 encrypt with key2 3> create database encr_db3 encrypt with key3 4> go 1> sp_encryption helpkey, '%', "display_dbs" Key Name Key Owner Encrypted Database -------------------- -------------------------------------- key1 dbo encr_db1 key1 dbo encr_db2 key3 dbo encr_db3