How to test whether current sql engine service account can decrypt the SMK?

Question

Is it true that when the sql service account is changed via the config manager then the new account can decrypt the SMK.

However if the change was made via the services.msc, then the new account doesn't have access to the SMK?

And that in the later case - sensitive data that is encrypted by SMK still continues to work because the computer account is able to access the SMK always. However when such a master db is moved to another server then and assuming that server has the sql service account different to the account that can decrypt the SMK then the linked server, credentials will not work?

How can I test whether a service account can access the SMK or not?

Answer 1

How can I test whether a service account can access the SMK or not?

Try this to open and close the master key.

USE <your database>
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<your password>'
GO
CLOSE MASTER KEY
GO

If you would like to re-encrypt your master key with new SMK. Then you could do this,

USE <your database>
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<your password>'
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
CLOSE MASTER KEY
GO