With two databases A and B on the same SQL Server 2014 instance, I've written a series of scripts trying to do the following (written in very high level pseudo code):
User Updates table
A.dbo.Mainhas a trigger on
UPDATE, DELETE, INSERTthat calls a stored procedure
A.CallProcInBthen calls a stored Procedure in B called
B.RunComponentsUpdatethen merges a view and a table called
The problem is I have gotten these series of scripts to work perfectly on a personal computer running SQL Server 2014 Express, with the exact same schema setup for the databases A and B, but when I set everything up on my company's main server running SQL Server 2014 I get the following error message:
ERROR  [Microsoft][ODBC SQL Server Driver][SQL Server]
The SELECT permission was denied on the object 'B_Table', database 'B', schema 'dbo'. ERROR  [Microsoft][ODBC SQL Server Driver][SQL Server]
The UPDATE permission was denied on the object 'B_Table', database 'B', schema 'dbo'.
I've tried giving the user activating the initial trigger all privileges on both A and B, as this was enough to fix the issue on my personal computer server. However, giving basically "god" privileges hasn't been enough to resolve this error on the server.