Where is this job's RUN AS context hidden?
-
i've got a suite of SSIS jobs that have job steps that explicitly say they will run as [SQL Server Agent Account];
I've got other TSQL only job steps that leave the run as blank.
I THOUGHT the run as user was sysjobsteps.database_user_name, but that doesn't make sense, since the agent is not a database user.
for my specific job step above, i get some data, but the name was blank.
i searched column names for anything that contained "run", and couldn't find it that way either.
where is the run as user stored for a given step?
oh, and why do i want this info? i want to find any job steps, on any server, that might have users that should not be used, and should be replaced by a different user;
/*
database_name database_user_name
NULL NULL
*/
SELECT
jobz.name,
stepz.step_name,
stepz.database_name,
stepz.database_user_name
FROM msdb.dbo.sysjobs jobz
INNER JOIN msdb.dbo.sysjobsteps stepz
ON jobz.job_id = stepz.job_id
WHERE stepz.step_name = 'CDW_GDC-SQL-D01_MSSQLSERVER1_GDC-SQL-D01_MSSQLSERVER1_0_2_Step'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
"where is the run as user stored for a given step?"
You can find the "run as" for a job step in the column [proxy_id] in [msdb].dbo.[sysjobs]
Its text value is found in the [name] column, in [msdb].dbo.[sysproxies]. Example query:
USE [msdb]
GO
SELECT JobName = j.[name]
,JobOwner = suser_sname(owner_sid)
,ProxyName = Prx.[name]
,js.*
FROM [dbo].[sysjobsteps] js WITH(NOLOCK)
INNER JOIN dbo.sysjobs j
ON js.job_id = j.job_id
LEFT OUTER JOIN sysproxies AS Prx
ON js.proxy_id = Prx.proxy_id
------WHERE js.[subsystem] = 'CmdExec'
ORDER BY j.[name], step_id
See:
HTH,
--- Doug
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply