How to find SQL Server proxy login

A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL Server Agent subsystem, and then assign that proxy to the job step.

---->>proxy login
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
SQL Server Agent Proxy

How to create a SQL Server Agent proxy in SQL Server

-- creates credential CatalogApplicationCredential  
USE msdb ;  
GO  
CREATE CREDENTIAL CatalogApplicationCredential WITH IDENTITY = 'REDMOND/TestUser',   
    SECRET = 'G3$1o)lkJ8HNd!';  
GO  
-- creates proxy "Catalog application proxy" and assigns
-- the credential 'CatalogApplicationCredential' to it.  
EXEC dbo.sp_add_proxy  
    @proxy_name = 'Catalog application proxy',  
    @enabled = 1,  
    @description = 'Maintenance tasks on catalog application.',  
    @credential_name = 'CatalogApplicationCredential' ;  
GO  
-- grants the proxy "Catalog application proxy" access to 
-- the ActiveX Scripting subsystem.  
EXEC dbo.sp_grant_proxy_to_subsystem  
    @proxy_name = N'Catalog application proxy',  
    @subsystem_id = 2 ;  
GO

Reference: https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver15

Generally used in Agent jobs

Spread the love

Leave a Comment