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

Question

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 sysadmin when running the script):

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TestJob', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestStep', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SELECT TOP 1 * FROM TestDatabase2.dbo.TestTable', 
        @database_name=N'TestDatabase1', 
        @database_user_name=N'TestUser', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Once the job is created I run it from SSMS(I'm connected as a SQL sysadmin when doing this). The job fails with the following error:

Date        10/04/2012 3:26:31 p.m.
Log     Job History (TestJob)

Step ID     1
Server      obfuscated
Job Name        TestJob
Step Name       TestStep
Duration        00:00:00
Sql Severity        14
Sql Message ID      916
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0

Message
Executed as user: TestUser. The server principal "TestUser" is not able to access the database "TestDatabase2" under the current security context. [SQLSTATE 08004] (Error 916).  The step failed.

And here is the clean up script to remove the database objects created by the scripts above:

USE [master]
GO

alter database TestDatabase1 set single_user with rollback immediate
GO

alter database TestDatabase1 set multi_user
GO

alter database TestDatabase2 set single_user with rollback immediate
GO

alter database TestDatabase2 set multi_user
GO

drop database TestDatabase1
GO

drop database TestDatabase2
GO

USE [msdb]
GO

declare @job_id uniqueidentifier
SELECT @job_id = job_id FROM msdb.dbo.sysjobs_view WHERE name = N'TestJob'

EXEC msdb.dbo.sp_delete_job @job_id=@job_id, @delete_unused_schedule=1
GO

DROP LOGIN [TestUser]
GO

Questions:

1. Why different results from a job and from SSMS?
2. How do I make the job work (instead of failing)?

UPDATE 1

Using advanced google-fu I was able to determine that one of the answers to Question 2 can be this:

ALTER DATABASE TestDatabase1 SET TRUSTWORTHY ON;
GO
RECONFIGURE WITH OVERRIDE;
GO

Question 1 still remains unanswered

UPDATE 2

Ok, I think I got it, I posted an answer below. However one thing is still not clear. This error from the job, it does not happen in SQL 2005. It happens in SQL 2008 but not in SQL 2005. Obviously something has changed. Anyone knows what exactly was the change?

Answer 1

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.

Answer 2

I faced the same problem and at first, I solved the problem by turning on the TRUSTWORTHY property on the database. But, I find it not a safe way in terms of security.

The best practice to tackle this problem is by going to the job steps "-->Advanced-->" make it blank under "Run as user". When you provide a login account under "Run as user", you are telling the SQL Agent to impersonate that account and that is the main reason you are getting this error response.

By design from Microsoft, impersonation is disabled. To enable impersonation, you can enable the trustworthy as shown below:

ALTER DATABASE TestDatabase1 SET TRUSTWORTHY ON;
GO
RECONFIGURE WITH OVERRIDE;
GO

Hope this will help you.

Answer 3

When you're running a job, and you've not specified a user the job runs as, the job runs under the SQL Server service, which may or may not have permissions on your database.

When you're running a script under SSMS, it runs as the user you're logged in as, which may or may not have permissions on your database.

This is the difference in the results. You have to specify in the job what user credentials it will use.