Cross-database call fails in a job but succeeds in SSMS
Since no one posting feedback as an answer:
Why different results from a job and from SSMS?
Apparently SQL Agent uses EXECUTE AS USER to run job steps if you specify a user with @database_user_name
parameter to sp_add_jobstep
stored procedure. In my example above this behaviour can be replicated in SSMS by logging in as a sysadmin and running this script:
use TestDatabase1;
GO
execute as user = 'TestUser';
GO
select top 1 * from TestDatabase2.dbo.TestTable;
GO
REVERT
GO
Note, that if we change execute as user
to execute as login
in this code snippet, the error go away, but apparently sql agent uses execute as user
.
According to MSDN: While the [EXECUTE AS USER] context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database that uses three- or four-part identifiers.
More information is at helpful @RemusRusanu link: Trouble With Database Permissions for sp_send_mail
How do I make the job work (instead of failing)?
ALTER DATABASE TestDatabase1 SET TRUSTWORTHY ON;
GO
RECONFIGURE WITH OVERRIDE;
GO
Other options are also considered in the Remus Rusanu's link above.