How long for a change in user context to filter through to SQL Agent?

As mentioned in my post on Which Context SQL Agent uses to execute a T-SQL step in a job I mentioned that SQL Agent doesn’t instantly pick up on a users context change. Being naturally curious I decided to see how long it actually took. So here’s a couple of test scripts I used. There were written to be used in SQL Server management studio so you could run them in individual query windows.

The first script sets up a simple logging table in a database of your choice and grants a user insert on this table (here I’m using a standard windows user with no roles other than Public). Then we switch to msdb to build a job, and then fire this job off every minute for 80 minutes:

use db1

CREATE TABLE permtest(
	[ologin] [varchar](15) NULL,
	[suser] [varchar](15) NULL,
	[dbuser] [varchar](15) NULL,
	[tdate] [datetime] NULL
) ON [PRIMARY]

grant insert on permtest to [Domain\dbuser]

GO
*/

use msdb

DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'perm test3',
		@owner_login_name=N'Domain\dbuser',
		@enabled=1,
		@job_id = @jobId OUTPUT
EXEC 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'insert into permtest (ologin,suser,dbuser,tdate) select cast(ORIGINAL_LOGIN() as varchar(15)), cast(SUSER_NAME() as varchar(15)), cast(USER_NAME() as varchar(15)),getdate()',
		@database_name=N'db1',
		@flags=20

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

exec sp_start_job N'perm test3'
BEGIN
waitfor delay '00:01:00'
exec sp_start_job N'perm test3'
END
GO 80

Then in another query window we have:

waitfor delay '00:05:00'
EXEC master..sp_addsrvrolemember @loginame = N'Domain\dbuser', @rolename = N'sysadmin'
insert into permtest (ologin,suser,dbuser,tdate) select 'role add','role add','role add',getdate()
waitfor delay '00:30:00'
insert into permtest (ologin,suser,dbuser,tdate) select 'role drop','role drop','role drop',getdate()
EXEC master..sp_dropsrvrolemember @loginame = N'Domain\dbuser', @rolename = N'sysadmin'
waitfor delay '00:30:00'
select * from permtest order by tdate asc

Run the script in window 1, then switch to window 2 and run that script. Then leave it for 80 minutes. Once that’s done, you’ll have a result set something like this:

ologin suser dbuser tdate
Domain\sqlagent Domain\dbuser Domain\dbuser 2012-05-03 10:35:05.677
–**–**– –**–**– –**–**– –**–**–
Domain\sqlagent Domain\dbuser Domain\dbuser 2012-05-03 10:40:05.590
Domain\sqlagent Domain\dbuser Domain\dbuser 2012-05-03 10:41:05.487
role add role add role add 2012-05-03 10:41:08.743
Domain\sqlagent Domain\dbuser dbo 2012-05-03 10:42:05.493
Domain\sqlagent Domain\dbuser dbo 2012-05-03 10:43:05.493
Domain\sqlagent Domain\dbuser dbo 2012-05-03 10:44:05.497
–**–**– –**–**– –**–**– –**–**–
omain\sqlagent Domain\dbuser dbo 2012-05-03 11:00:05.610
Domain\sqlagent Domain\dbuser dbo 2012-05-03 11:01:05.630
Domain\sqlagent Domain\dbuser dbo 2012-05-03 11:02:05.640
Domain\sqlagent Domain\dbuser dbo 2012-05-03 11:03:05.643
Domain\sqlagent Domain\sqlagent dbo 2012-05-03 11:04:05.670
–**–**– –**–**– –**–**– –**–**–
Domain\sqlagent Domain\sqlagent dbo 2012-05-03 11:10:05.727
Domain\sqlagent Domain\sqlagent dbo 2012-05-03 11:11:05.750
role drop role drop role drop 2012-05-03 11:11:08.743
Domain\sqlagent Domain\sqlagent dbo 2012-05-03 11:12:05.737
Domain\sqlagent Domain\sqlagent dbo 2012-05-03 11:13:05.740
–**–**– –**–**– –**–**– –**–**–
Domain\sqlagent Domain\sqlagent dbo 2012-05-03 11:31:05.870
Domain\sqlagent Domain\sqlagent dbo 2012-05-03 11:32:06.877
Domain\sqlagent Domain\dbuser Domain\dbuser 2012-05-03 11:33:07.070
–**–**– –**–**– –**–**– –**–**–
Domain\sqlagent Domain\dbuser Domain\dbuser 2012-05-03 11:40:06.947
Domain\sqlagent Domain\dbuser Domain\dbuser 2012-05-03 11:41:06.957

“–**–**–” shows where I’ve removed some rows just to make the table a little smaller.

From this you can see that SQL Agent instantly recognises the change in database permissions (from dbuser to dbo), but takes approximately 23 minutes to change the user context. And it’s the same scenario when the users role is revoked. I’ve run this script on a number of my instances and they all appear to be around the 20 minute mark, but it’s probably a good idea to check with your instances if it’s likely to be a problem.

This shouldn’t be too much of a problem, unless the query relies on the identity of the user calling it (Microsoft’s Dynamics CRM’s filtered view are a widely used example of this). With these filtered views you could find that 20 minutes after your new job’s gone live it’s suddenly returning an different set of data (or even none at all).

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