Lists permissions for SQL Server Agent proxies to access subsystems.
Applies to: SQL Server (SQL Server 2008 through current version).
When no parameters are provided, sp_enum_proxy_for_subsystem lists information about all proxies in the instance for every subsystem.
When a proxy id or proxy name is provided, sp_enum_proxy_for_subsystem lists subsystems that the proxy has access to. When a subsystem id or subsystem name is provided, sp_enum_proxy_for_subsystem lists proxies that have access to that subsystem.
When both proxy information and subsystem information is provided, the result set returns a row if the proxy specified has access to the subsystem specified.
This stored procedure is located in msdb.
A. Listing all associations
The following example lists all permissions established between proxies and subsystems for the current instance.
USE msdb ; GO EXEC dbo.sp_enum_proxy_for_subsystem ; GO
B. Determining if a proxy has access to a specific subsystem
The following example returns a row if the proxy Catalog application proxy has access to the ActiveScripting subsystem. Otherwise, the example returns an empty result set.
USE msdb ; GO EXEC dbo.sp_enum_proxy_for_subsystem @subsystem_name = 'ActiveScripting', @proxy_name = 'Catalog application proxy' ; GO