Over time it's easy to lose track of the things you set up to get work done. As a consultant, I have to discover what's set up on a client system. In either case, it's important to know what security settings are in place to know if they're appropriate and well managed.

One of the hidden gems (and potential nightmares) in SQL Server is the use of proxies to allow job steps to acquire a different set of privileges than would normally be allowed. (I blogged about setting up proxies here. I use them when I need a process to have access to WMI, for example, to gather information from the operating system on a regular basis. The service account I use for the Agent service doesn't have that privilege normally.

So, to keep track of what's been set up, I've got a PowerShell script which returns all proxies, by Job and Step. First I load the SMO assemblies and connect to the SQL Server instance.

# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
  }
# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'

Next, I get the ProxyAccounts collection from the instance's JobServer object and show the CredentialName and CredentialIdentity properties for each proxy created.

# Get the ProxyAccounts collection from the JobServer object
$pa = $s.JobServer.ProxyAccounts
# Return the Credential Identity for all proxies created
$pa | select Name, CredentialName, CredentialIdentity

And finally I cycle through the jobs and look at each job step, and report which step uses which proxy.

# Get the Jobs and return all proxies used by each job
$jobs = $s.JobServer.Jobs
foreach ($job in $jobs) {
[string]$jobnm = $job.Name
foreach ($step in $job.JobSteps) {
$ln = $step.ProxyName.Length
if ($ln -gt 0) {
[string]$nm = $step.Name
[string]$px = $step.ProxyName
write-output "Job: $jobnm, Step: $nm, Proxy Used: $px"
}
}
}

This gives me a quick view to see where the proxies are being used, and lets me focus my energies on where it's important.

Allen