SSMS 2008R2 and 2016 – Can’t Script Database As CREATE TO

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.

This code executed for me fine. The only issue I might suspect is that your server is case sensitive. The EXISTS check will come up empty if you have a login named cert01login but the create will fail because it is not case sensitive. So you'll want to make sure the EXISTS check is not case sensitive, e.g.

WHERE name COLLATE Latin1_General_CI_AI = @cert_name + 'Login'