How to find SQL Server proxy login

Understanding SQL Server Agent Proxy Accounts

In SQL Server, an Agent proxy account provides a specific security context under which a job step can execute. Essentially, each proxy is linked to a security credential, which defines the permissions the job step will use. This is especially useful when you need to control permissions for individual steps within SQL Server Agent jobs.

To assign permissions to a particular job step, you create a proxy with the necessary rights for a SQL Server Agent subsystem and then associate that proxy with the job step.

---

Viewing Job Steps and Their Proxies

The following query helps you list SQL Server Agent jobs along with their steps and the associated proxy accounts:

---->> List job steps with their assigned proxies
SELECT 
    J.job_id,    
    J.name,    
    S.step_name,    
    S.step_id,    
    P.name AS ProxyName,    
    SP.name AS CredentialUserName,    
    SP.type_desc AS CredentialUserType
FROM 
    msdb.dbo.sysjobs J
    INNER JOIN msdb.dbo.sysjobsteps S ON S.job_id = J.job_id
    LEFT OUTER JOIN msdb.dbo.sysproxies P ON P.proxy_id = S.proxy_id
    LEFT OUTER JOIN sys.server_principals SP ON SP.sid = P.user_sid;

This output shows which proxies are linked to which job steps, including the credential user associated with each proxy.

---

How to Create a SQL Server Agent Proxy

Here is an example script to create a SQL Server Agent proxy:

1. Create a Credential that stores the security context (username and password).
2. Create a Proxy that uses the credential.
3. Grant the Proxy permission to access a specific subsystem, such as ActiveX Scripting.

-- Step 1: Create a credential
USE msdb;
GO
CREATE CREDENTIAL CatalogApplicationCredential 
WITH IDENTITY = 'REDMOND/TestUser',
SECRET = 'G3$1o)lkJ8HNd!';
GO

-- Step 2: Create a proxy and assign the credential to it
EXEC dbo.sp_add_proxy  
    @proxy_name = 'Catalog application proxy',
    @enabled = 1,
    @description = 'Maintenance tasks on catalog application.',
    @credential_name = 'CatalogApplicationCredential';
GO

-- Step 3: Grant the proxy access to the ActiveX Scripting subsystem (subsystem_id = 2)
EXEC dbo.sp_grant_proxy_to_subsystem  
    @proxy_name = N'Catalog application proxy',
    @subsystem_id = 2;
GO

---

When to Use SQL Server Agent Proxies

Proxies are generally used within SQL Server Agent jobs to allow specific job steps to run under different security contexts. This is particularly useful when some job steps require elevated or restricted permissions distinct from the SQL Server Agent service account.

---

Reference

For more detailed information, visit the official Microsoft documentation:
Create a SQL Server Agent Proxy

---

Using SQL Server Agent proxies helps you secure your automated jobs by controlling the exact permissions under which each step runs. This improves security and allows for more flexible job management.