3

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.. – TheGameiswar Mar 7 '16 at 14:07
  • select suser_sname(owner_sid) from msdb.dbo.sysjobs – TheGameiswar Mar 7 '16 at 14:07
  • 1
    I think the OP is asking for the Proxy account used for each step, not the job owners. – Jonathan Fite Mar 7 '16 at 14:11
6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.