I was wondering if there is an easy way to check the "Run as" value for each job step using TSQL code instead of opening every job on the server and each individual step? I cannot figure out where to get this data from and I have scoured the MSDB in search of this but cant find anything in the sysjobs and related tables? SQL has to store this value somewhere. The question is where?
3
-
Does this help,i dont have acces to check currently..– TheGameiswarCommented Mar 7, 2016 at 14:07
-
select suser_sname(owner_sid) from msdb.dbo.sysjobs– TheGameiswarCommented Mar 7, 2016 at 14:07
-
1I think the OP is asking for the Proxy account used for each step, not the job owners.– Jonathan FiteCommented Mar 7, 2016 at 14:11
Add a comment
|
1 Answer
This should get you close, if NULL for ProxyName then it's using the SQL Server Agent credentials.
USE msdb
SELECT J.job_id
, J.name
, S.step_name
, S.step_id
, P.name AS ProxyName
, SP.name AS CredentialUserName
, SP.type_desc AS CredentialUserType
FROM msdb.dbo.sysjobs J
INNER JOIN msdb.dbo.sysjobsteps S ON S.job_id = J.job_id
LEFT OUTER JOIN msdb.dbo.sysproxies P ON P.proxy_id = S.proxy_id
LEFT OUTER JOIN sys.server_principals SP ON SP.sid = P.user_sid
answered Mar 7, 2016 at 14:10
