How to get the AD account associated to a Proxy?

Question

on the picture below, you can see, I have a sql server job called "Backup SSAS DBS_" that Run as: PowerShell_Proxy

How can I find out the AD (active directory) account associated with that Proxy via T-SQL?

SO far I have this script below, but something is missing. Also, the AD account might not be a login or user in the current server.

USE [msdb]
GO

select 
s.job_id
,s.name as [job_name]
,js.step_id
,js.step_name
,js.subsystem
,js.proxy_id
,js.command
from sysjobs S
INNER JOIN sysjobsteps JS ON S.job_id = js.job_id
LEFT OUTER JOIN sysproxylogin p ON js.proxy_id = p.proxy_id

WHERE S.NAME LIKE '%' + 'SSAS' + '%'

Using the query below I can get the SID used by the proxy.

select * from sysproxylogin

Answer 1

I think this is what you need.

select 
    s.job_id
    ,s.name as [job_name]
    ,js.step_id
    ,js.step_name   
    ,js.subsystem
    ,js.proxy_id
    ,js.command
    , c.credential_identity
from sysjobs S
INNER JOIN sysjobsteps JS ON S.job_id = js.job_id
INNER JOIN sysproxies p ON js.proxy_id = p.proxy_id
INNER JOIN sys.credentials as c on c.credential_id = p.credential_id 

The proxy_id in sysjobsteps comes from sysproxies. You then get the account from credential_id in sys.credentials which is also available in sysproxies.