It seems to be a weird error, I have tried everything from giving Control, Alter, and View Definition permission on the symmetric key but nothing seems to work out.

Tried everything from this article. https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-symmetric-key-permissions-transact-sql?view=sql-server-ver16

It works perfectly fine when I grant sysadmin permission to the user, however not working with db_owner and db_datareader roles. A user with sysadmin privilege can access it without any issues.

Have tried running the following alter command on the DB as well but still no go.

alter master key add encryption by service master key

  • You'll have to go through the users permissions, roles, etc., and see what is denying them access. When someone is given sysadmin, permissions checks are ignored which is why it works. Oct 2 at 19:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.