2012-01-14 22:16:26.47 spid20s Please create a master key in the database or open the master key in the session before performing this operation.
alter master key drop encryption by service master key
open master key DECRYPTION BY PASSWORD = 'password'
alter master key add encryption by service master key
select is_master_key_encrypted_by_server from sys.databases where name = 'master'
|Rule software||Rule title||Rule description||Product versions against which the rule is evaluated|
|Microsoft System Center Advisor||SQL Server database master key for the master database is not encrypted by the service master key.||System Center Advisor checks if the instance of SQL Server has any databases that use TDE. It also checks to make sure that the database master key of the master database is encrypted by the service master key. If this is not the case, System Center Advisor generates a warning.||SQL Server 2008|
SQL Server 2008 R2
SQL Server 2012
Article ID: 2666213 - Last Review: Feb 13, 2012 - Revision: 1