SQL Server Agent Job: running under sysadmin, but still permissions denied

Last week an interesting issue popped-up when I was working on an SQL Server environment migration. A SQL Server Agent Job was throwing an exception, but we didn’t know what the issue was. And it worked in the old environment…

 
Finding the problem
During the installation of the new hardware, we used new accounts for everything (security reasons). This could mean a lot of issues with permissions, but gladly these issues weren’t that bad. Until we found the problem with the job, that threw this exception:

 
Looking at the service account, I saw that this account had the sysadmin role. So why did it threw the exception? After some research, I found out why.

 
Creating the resources
In order to test this, we need to create a SQL login:

USE [master]
GO

CREATE LOGIN [TEST_JEFFREY] WITH PASSWORD=N'Test!', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

 
And a SQL Server Agent Job:

USE [msdb]
GO

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

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TEST JEFFREY', 
		@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'[Monitoring]', 
		@owner_login_name=N'TEST_JEFFREY', @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'TEST', 
		@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 1
WAITFOR DELAY ''00:30:00''', 
		@database_name=N'master', 
		@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

If we run the job now, and use sp_WhoIsActive to check out which user it runs under, it shows that the job runs under the SQL user:

 
This seems strange, because the SQL Server Agent service runs under a different account, and that account is sysadmin on the instance. But what if I add the sysadmin role to the SQL account we just created to reproduce this issue?

ALTER SERVER ROLE [sysadmin] ADD MEMBER [TEST_JEFFREY]
GO

 
And now run the job again:

 
When the SQL account is sysadmin, it does run under the configured SQL Server Agent account.

 
Check it to be sure
More information about this issue can be found here and here. To me, it was a bit unclear why this went wrong, but I’m glad I got some help on this (thanks Mark!). But when you encounter this yourself, or when you’re creating new jobs, make sure you check this. It could save you valuable time.