T-SQL: Add/drop pass to open a database master key

When SQL Server needs a database master key to decrypt or encrypt a key, SQL Server tries to decrypt the database master key with the service master key of the instance.
If the decryption fails, SQL Server searches the credential store for master key credentials that have the same family GUID as the database for which it needs the master key. SQL Server then tries to decrypt the database master key with each matching credential until the decryption succeeds or there are no more credentials.

To add the master password:

EXEC sp_control_dbmasterkey_password @db_name = N'User Database Name here', @password = N'passwordrequiredhere', @action = N'add';
GO

To remove the master password:

EXEC sp_control_dbmasterkey_password @db_name = N'User Database Name here', @password = N'passwordrequiredhere', @action = N'drop';
GO 

Default image
Bart
Articles: 87