if you lose keys, it's no trouble , since we can regenerate, right?
Here is the scenario: you are new DBA and master key is opened by service master key.
SELECT * FROM sys.openkeys;
You do not know the password this master key was generated but it's ok, you can always add another password to open it.
ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'another strong password';
Verify this password works by
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'another strong password';
Feel free to backup/restore this database master key to another database on another server and open symmetric keys using this restored master key.
You can check number of passwords in
SELECT * FROM sys.key_encryptions;
Can you drop the password?
Sure.
ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = 'another strong password';
Can you drop initial password or change password without knowing it?
Nope, in this case you should regenerate master key removing all password except the one you created.
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'new password';
Regenerate is resource-intensive operation it will decrypt everything with old key, generate new keys and encrypt everything with new keys. So if you have a lot of data encrypted with symmetric keys it could take a while.
Also, you can remove auto-open feature by removing ENCRYPTION BY service master key just to simulate behavior of freshly restored database.
ALTER MASTER KEY DROP ENCRYPTION BY service master key;
Now if you close your master key
CLOSE MASTER KEY;
you have to provide the password to open it
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'new password';
Now you can turn on 'auto-open'
ALTER MASTER KEY ADD ENCRYPTION BY service master key;
