SQL Server 2005 – Database Master Key

If the Database Master Key was encrypted with the Service Master Key, it will be automatically opened when it is needed for decryption or encryption. In this case, it is not necessary to use the OPEN MASTER KEY statement.

When a database is first attached to a new instance of SQL Server, a copy of the Database Master Key (encrypted by the Service Master Key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the Database Master Key. Once the Database Master Key has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY statement to provision the server with a copy of the Database Master Key encrypted with the Service Master Key.

You enable automatic decryption of the database master key by executing the following statement:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '...'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

ADD ENCRYPTION BY SERVICE MASTER KEY causes a copy of the master key to be encrypted using the service master key and stored in both the current database and in master.

References:

1. MSDN, http://msdn2.microsoft.com/en-us/library/ms174433(SQL.90).aspx

2. MSDN, http://msdn2.microsoft.com/en-us/library/ms187580(SQL.90).aspx