In what user context does a job run? I recently found myself in a forum discussion and gave my stock reply, later realizing that I haven't actually tested this for a long time (I used to demo this in class during 6.5 courses - when we actually had time for slight diversions). Lets start with my assumptions:
- Job owned by sysadmin, TSQL jobsteps:
Agent log in to SQL Server using its own credential (windows authentication from service account) and execute the TSQL commands - no impersonation performed.
- Job owned by sysadmin, other jobsteps:
Agent starts a process using its service account - no impersonation performed.
- Job owned by non-sysadmin, TSQL jobstep:
Agent log in to SQL Server using its own credential (windows authentication from service account), then uses EXECUTE AS LOGIN = '<job_owner_login>' to "impersonate" the login who owns the job. I.e., the TSQL commands will be executed in the security context of the job owner's login.
- Job owned by non-sysadmin, other jobsteps:
Agent starts a process using the service account as specified by the Agent proxy selected for this jobstep. (See Books Online "sp_add_proxy" and "CREATE CREDENTIAL" and this for more information about Agent Proxies and credentials in SQL Server.)
So, how to prove above? Lets use a combination of jobstep output file and a Profiler trace. The ouput files will be used to catch the login/user names, and the Profiler trace to verify what TSQL commands will be submitted by Agent (for TSQL jobstep). For the TSQL jobsteps, we execute:
SET NOCOUNT ON
SELECT
CAST(ORIGINAL_LOGIN() AS VARCHAR(20)) AS Original_login
,CAST(SUSER_SNAME() AS VARCHAR(20)) AS Effective_login
,CAST(USER_NAME() AS VARCHAR(20)) AS Db_user
And to test the other jobsteps, where we will be using a CmdExec jobstep as example (we are interested in what Windows account the process will be started as), and use whoami.exe which returns the windows user name. Each jobstep is configured to output the result to a file.
-
I change my real machine name to "Machine" in this text.
-
I'm not in a domain.
-
I'm logged in interactively (to Windows) as Windows account "Tibor".
-
I'm logged in to SQL Server using Windows authentication.
-
I'm sysadmin.
-
My service account for both Agent and SQL Server services is "SQLService".
Here's the result from having the job owned by me (being sysadmin):
Original_login Effective_login Db_user
-------------------- -------------------- --------------------
MACHINE\SqlService MACHINE\SqlService dbo
machine\sqlservice
As you see, no attempted impersonation performed. Steps 1 - 2 in above list confirmed.
We now going to test this for somebody who isn't sysadmin:
-
Create SQL Server login named "Kalle"
-
Create user in msdb for above
-
Assign Kalle permission to create jobs (SQLAgentUser role in msdb)
-
Create account "MySqlProxy" in Windows.
-
Creade credential "c_MySqlProxy" in SQL Server for above.
-
Create proxy "p_MySqlProxy" for above, and allow login "Kalle" to use this for CmdExec jobsteps.
Now we modify the job and set the owner to Kalle, and also specify for the CmdExec jobstep to use the proxy p_MySqlProxy. Also, since non-sysadmins can't use output files (I didn't know that), we instead configure each step's output to go to table. Execute job. No, finally we can check each job step output for result:
Original_login Effective_login Db_user
-------------------- -------------------- --------------------
MACHINE\SqlService Kalle guest
machine\mysqlproxy
The first section above confirms that Agent uses it's own login, but then changes login context to Kalle (verified by Profiler trace capturing the "EXECUTE AS LOGIN = N'Kalle' WITH NO REVERT" command). The job step was configured to run in the master database; since I didn't add Kalle as a user to master, you see the user name guest.
The second section verifies that The CmdExec step started a process using the mysqlproxy windows account, and specified using our SQL Server Agent Proxy and SQL Server credential object.
(I will not reply to support questions here. For support, I recommend you visit a forum, for instance this. Other discussions, comments and corrections are of course very welcome!)
Finally, I scripted out the job (as owned by me - being sysadmin), if you want to re-create it and play with it. Use at own risk, do apropriate modifications etc.
USE [msdb]
GO
/****** Object: Job [CredTest] Script Date: 09/19/2009 12:12:47 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 09/19/2009 12:12:47 ******/
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'CredTest',
@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'MACHINE\Tibor', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [A_TSQL] Script Date: 09/19/2009 12:12:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'A_TSQL',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@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'SET NOCOUNT ON
SELECT
CAST(ORIGINAL_LOGIN() AS VARCHAR(20)) AS Original_login
,CAST(SUSER_SNAME() AS VARCHAR(20)) AS Effective_user
,CAST(USER_NAME() AS VARCHAR(20)) AS Db_user',
@database_name=N'master',
@flags=8
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [B_OS] Script Date: 09/19/2009 12:12:48 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'B_OS',
@step_id=2,
@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'CmdExec',
@command=N'whoami.exe',
@flags=16
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