This is happening right after we did a migration to a new server and went from 2012 to 2019, so I think it might have something to do with it.
When I ran the below code on my ID on the new server, or on the previous installation of SQL under the user that runs the app(BlahUser), it works fine. However, under the new server it throws a Cannot find the symmetric key 'master key', because it does not exist or you do not have permission. error under BlahUser.
open master key decryption by password = 'Passphrase'
open symmetric key BlahKey
decryption by certificate BlahCertificate;
I have tried running the following on the database in question, in case permissions haven't been migrated properly:
GRANT control ON SYMMETRIC KEY::[BlahKey] TO BlahUser;
GO
grant control on Certificate::BlahCertificate to BlahUser;
go
and that has had no effect. Any ideas?
Adding this edit for extra info:
I took a look at sys.symmetric_keys and when I did a select on my Login, vs the app login(BlahUser), I noticed that sys.symmetric_keys was present on my login, but not present on the app login. I ran
GRANT control ON SYMMETRIC KEY::[##MS_DatabaseMasterKey##] to BlahUser
but it has had no effect other than showing up in the resultset for sys.symmetric_keys--same error otherwise.