Sql-server – How to get the AD account associated to a Proxy

active-directoryjobssql serversql server 2014

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

enter image description here

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' + '%'

enter image description here

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

select * from sysproxylogin

enter image description here

Best Answer

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.

Related Question