3

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

0

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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