snippetsqlMinor

How to find what SQL Jobs are using a specific account as Proxy?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

proxyhowwhatsqlareaccountjobsusingfindspecific

Problem

We have identified a few servers that are using Proxy accounts inappropriately. Some of these servers have multiple Credentials and Lots of Jobs. Manually checking the Job Property GUI for "Run As" on each step is not desired.

How can I quickly identify which if any, jobs have steps that are using Proxy Credentials we have identified as inappropriate?

I want to see, the account related to the Proxy. As well as the Job Name and Step the Proxy is used on.

SQL 2008+

Solution

The servers I was looking at had only few credentials. While they had several jobs only a handful were using “Run As” Proxy credentials. This is the solution I used.

First query shows the account (credential identity) linked to the proxy
Second Query shows what Job and Step is using a Proxy

-- Search Credentials (shows account for Name)

    use msdb
    select *
    from sys.credentials

    --Search Jobs where there is a 'Run As' proxy and get the name of that proxy

    use msdb

    select  sysjobsteps.job_id
    , sysjobs.name as 'JobName'
    , sysjobsteps.step_id
    , sysjobsteps.step_name
    , sysjobsteps.subsystem
    , sysjobsteps.last_run_date
    , sysjobsteps.proxy_id
    --, sysjobsteps.step_uid
    , sysproxies.name as 'ProxyName'

    from sysjobsteps
    left join dbo.sysproxies
     on sysjobsteps.proxy_id = sysproxies.proxy_id
    left join dbo.sysjobs
     on sysjobsteps.job_id = sysjobs.job_id

    where sysjobsteps.proxy_id > 0

I tried a couple of ways of joining sys.credentials to dbo.sysproxies. If there was more than one credential it did not work well. Separate quires met my needs so I did not dwell on the join.

Code Snippets

-- Search Credentials (shows account for Name)

    use msdb
    select *
    from sys.credentials

    --Search Jobs where there is a 'Run As' proxy and get the name of that proxy

    use msdb

    select  sysjobsteps.job_id
    , sysjobs.name as 'JobName'
    , sysjobsteps.step_id
    , sysjobsteps.step_name
    , sysjobsteps.subsystem
    , sysjobsteps.last_run_date
    , sysjobsteps.proxy_id
    --, sysjobsteps.step_uid
    , sysproxies.name as 'ProxyName'


    from sysjobsteps
    left join dbo.sysproxies
     on sysjobsteps.proxy_id = sysproxies.proxy_id
    left join dbo.sysjobs
     on sysjobsteps.job_id = sysjobs.job_id

    where sysjobsteps.proxy_id > 0

Context

StackExchange Database Administrators Q#137675, answer score: 5

Revisions (0)

No revisions yet.