SQL Server Agent Job: running under sysadmin, but still permissions denied
December 2, 2014 2 Comments
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:
The UPDATE permission was denied on the object ‘[TableName]’, database ‘[DatabaseName]’, schema ‘[SchemaName]’.
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.