Cross-database call fails in a job but succeeds in SSMS

I create two databases, a table in the second database and a stored procedure in the first database. The stored procedure cross-database accesses the table. I create a sql server login and I also map this login to a user in each of the databases. I give db_owner permission to the users. Here is the script that accomplishes it (I'm connected as a SQL sysadmin when running the script):

USE [master]
GO

CREATE DATABASE [TestDatabase1] ON  PRIMARY 
( NAME = N'TestDatabase1', FILENAME = N'd:\database\TestDatabase1.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDatabase1_log', FILENAME = N'd:\database\TestDatabase1_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

CREATE DATABASE [TestDatabase2] ON  PRIMARY 
( NAME = N'TestDatabase2', FILENAME = N'd:\database\TestDatabase2.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDatabase2_log', FILENAME = N'd:\database\TestDatabase2_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

USE [TestDatabase2]
GO

CREATE TABLE [dbo].[TestTable](
    [Test] [int] NULL
) ON [PRIMARY]
GO

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[TestDatabase1], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestDatabase1]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [TestDatabase1]
GO
ALTER USER [TestUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [TestDatabase2]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [TestDatabase2]
GO
ALTER USER [TestUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [TestDatabase2]
GO
EXEC sp_addrolemember N'db_owner', N'TestUser'
GO
USE [TestDatabase1]
GO
EXEC sp_addrolemember N'db_owner', N'TestUser'
GO

Ones this is done, I connect to the server with SSMS under identity of the TestUser. I execute TestSp stored procedure in the SSMS and it succeeds.

Now I go ahead and create a job that executes the same stored procedure. I do it like this (I'm connected as a SQL

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.

StackExchange Database Administrators Q#16288, answer score: 6