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+
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.
You can also use the GUI to do a quick check on all jobs using a single proxy.
In object explorer navigate to the proxy
Right click on the proxy and select properties. Then go to the references tab.
That will list all jobs/job steps that use a given proxy.
Obviously a query is better for mass checks but this works for spot checks.
