How to grant SQL Agent proxy to a public role in MSSQL 2014

Posted on

Question :

I want to grant SQL Agent proxy to a public role in msdb database in MSSQL 2014.

I have the following query and I want this to return me some valid output.

USE [msdb] 
SELECT 
 sp.name AS proxyname 
FROM dbo.sysproxylogin spl 
JOIN sys.database_principals dp 
  ON dp.sid = spl.sid 
JOIN sysproxies sp 
 ON sp.proxy_id = spl.proxy_id 
WHERE principal_id = USER_ID('public');

Answer :

Before I answer your question I want to draw your attention about granting permissions to Public Msdb Role on the Proxy.

Reference: Best Practices in Configuring SQL Server Agent Proxy account.

Proper care must be taken when granting permissions to a security
principal on a Proxy especially when granting permissions to Public
Msdb Role on the Proxy. Every user in the database is a member of the
public role and as a result every user gains access to the Proxy when
Public Msdb role is granted access to the Proxy.
You can query the
sysproxylogin system table to get the list of SQL Server logins that
are associated with each SQL Server Agent proxy account.

You can achieve this by using the following TSQL (assuming your proxy is created properly).

USE msdb ;  
GO  

EXEC dbo.sp_grant_login_to_proxy  
    @msdb_role = N'public',  
    @proxy_name = N'Proxy Name' ;  
GO 

Details about using sp_grant_login_to_proxy.

Leave a Reply

Your email address will not be published. Required fields are marked *