SQL Agent impersonating users when running jobs and context errors

When running a SQL Server job that talks across databases within the same instance you can often come across:

Executed as user: Domain\user1. The server principal “Domain\user1” is not able to access the database “my_db1” under the current security context. [SQLSTATE 08004] (Error 916). The step failed.

And it won’t go away no matter how many privileges you throw at the offending users. Well, it turns out that is because a login/user with too many privileges won’t run the job as themselves, but will instead run it as the SQL Agent account, which may well not have the privileges you think it should.

And this can be a major problem if you’re using the job to extract data from Dynamics CRM filtered views as the SQL requires a valid Dynamics CRM licensed account to actually return any data.

Here’s a quick test script to show this.:

USE [msdb]
GO

declare @ReturnCode int
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'perm test2',
		@owner_login_name=N'Domain\dbuser',
		@enabled=1,
		@job_id = @jobId OUTPUT
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step 1',
		@step_id=1,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'select cast(ORIGINAL_LOGIN() as varchar(15)), cast(SUSER_NAME() as varchar(15)), cast(USER_NAME() as varchar(15))',
		@database_name=N'test_db',
		@flags=20

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id=@jobId, @server_name=N'(local)'

select @jobId

--Run with usual user permissions
exec sp_start_job N'perm test2'	

waitfor delay '00:00:30'

--Grant Sysadmin role
EXEC master..sp_addsrvrolemember @loginame = N'Domain\dbuser', @rolename = N'sysadmin'

waitfor delay '00:00:30'

exec sp_start_job N'perm test2'

--Longer wait for permissions to really kick in
waitfor delay '00:30:00'

exec sp_start_job N'perm test2'

waitfor delay '00:00:30'

--Drop sysadmin role
EXEC master..sp_dropsrvrolemember @loginame = N'Domain\dbuser', @rolename = N'sysadmin'

waitfor delay '00:00:30'

exec sp_start_job N'perm test2'

waitfor delay '00:30:00'

exec sp_start_job N'perm test2'

waitfor delay '00:00:30'

--Get results from log.
select a.log from sysjobstepslogs a, sysjobsteps b where a.step_uid=b.step_uid and b.job_id=@JobId

Going through this, we create a simple job with a single step. Domain\dbuser is a standard SQL Login based on a windows account with a specific user entry into the test db. We run the job a number of times whilst granting and then revoking the sysadmin role to Domain\dbuser.

We take the output from the logging table, format it a little better in notepad and get:

Job 'perm test2' : Step 1, 'step 1' : Began Executing 2012-04-30 10:28:18
--------------- --------------- ---------------
Domain\sqlagent Domain\dbuser   Domain\dbuser
(1 rows(s) affected)    

Job 'perm test2' : Step 1, 'step 1' : Began Executing 2012-04-30 10:29:18
--------------- --------------- ---------------
Domain\sqlagent Domain\dbuser   dbo
(1 rows(s) affected)    

Job 'perm test2' : Step 1, 'step 1' : Began Executing 2012-04-30 10:59:17
--------------- --------------- ---------------
Domain\sqlagent Domain\sqlagent dbo
(1 rows(s) affected)    

Job 'perm test2' : Step 1, 'step 1' : Began Executing 2012-04-30 11:00:17
--------------- --------------- ---------------
Domain\sqlagent Domain\sqlagent dbo
(1 rows(s) affected)    

Job 'perm test2' : Step 1, 'step 1' : Began Executing 2012-04-30 11:30:17
--------------- --------------- ---------------
Domain\sqlagent Domain\dbuser   Domain\dbuser
(1 rows(s) affected)

From this we can see that all actual logins to the DB are done under the Domain\sqlagent login, which is the windows account running my SQL Agent Service. Which makes sense as we’re running a job via it.

The interesting bit is who the SQL is running as and who it’s impersonating in the db.

1st Run – We’ve not done anything with our user so they appear as themselves and run under their own permission set in the db

2nd Run – Now we’ve granted serveradmin, they’re appear as themselve, but are running under the dbo permissions. No suprises here

3rd Run – Note that the script waits 30 minutes prior to this run. Now this is where it gets a little stranger. It appears that SQL Agent isn’t bothering to impersonate the user at all and just runs the query as itself.

4th Run – Role dropped from user, but SQL Agent hasn’t picked up on it yet

5th Run – Another 30 minute wait. And we’re back to where we were at the start.

So the login actually executing your job may not be the one you expect if you increase it’s permissions. It’s this that is being masked in the error message, the account that SQL Server claims can’t access the database under this context isn’t actually the account it’s using in this context. And it may even change after you first test it (a later post will investigate how long this takes) once SQL Agent picks up on the role

As always this shows that the best policy for sorting permission issues is to find the minimum level of permissions needed for a login to perform it’s job and only assign that. Many of the Server Roles give more access than most logins will ever need.

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

No comments yet.

Leave a Reply