The server principal "<AD Service Account>" is not able to access the database "<DBName>" under the current security context

Question

I've looked at the matches to this question title and the answers don't apply to me.
I'm using SQL Server 2016. I'm running a stored procedure using my account which is in the role sysadmin.
The stored procedure has WITH EXECUTE AS 'The account mentioned in the title'.
The stored procedure fails with the error in the title on a SELECT statement on a table in another DB from the one that stored procedure is running in.
The account is a user on both databases. In the database the sp is running in, it has Execute permission on the sp. In the database with the table it is attempting to SELECT from, it has SELECT permission on that table.
If I comment out the bit of code that does the SELECT, it then fails on a call to msdb..sp_send_dbmail with the error that the account doesn't have execute permission on that sp. That account is a member of the DatabaseMailUserRole Database Role in msdb. That Role includes in the Securables Execute permission on sp_send_dbmail.
Looking at the Login, it is included in the roles db_owner and db_datareader on the database it is failing to SELECT from.
I didn't set up this login/user so I'm not sure why it was done this way. I think that db_owner makes the db_datareader superfluous - is that right?
I checked the Event log on the SQL Server and it didn't include any entry for this error. Perhaps I'm looking at the wrong log?

UPDATE: I've found another database on the same server that has the same code. This one works therefore it's not the permission of that account on the database it is attempting to SELECT from.

That is, stored procedure runs in database DB-A with EXECUTE AS account AC1. Part of the stored procedure does a SELECT from database DB-B. That SELECT failed with the message that server principal AC1 is not able to access the database DB-B under the current security context.

I then run another stored procedure which is identical in code to the above one including executing as AC1 and accessing database DB-B but is in database DB-C. This one works. I'm really baffled as to what's going on.

Answer 1

Hi @Nick Ryan ,

This account should have access permission to these databases,

If your account is a Orphaned User? the SID in sys.sysusers is not mapped to the SID present in sys.syslogins, you can try to this command:

USE MyDB  
GO  
EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='sqluserName',@LoginName='sqluserName'  
GO  

MyDB is the name of database and sqluserName is the name of the user mapped with database MyDB

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Answered for me by our support DBA from Fujitsu. The reason it worked on the other database is that it has the Trustworthy setting enabled and the one I was trying to run from didn't. The DBA has now enabled the setting on my new database and everything works.

He sent me this link: https://learn.microsoft.com/en-us/sql/relational-databases/security/trustworthy-database-property?view=sql-server-ver16