Get Job step run for all jobs and job steps using TSQL

Question

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?

Answer 1

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