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] 
 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');

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 ;  

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

Details about using sp_grant_login_to_proxy.

| improve this answer | |
  • Thanks a lot for the quick response. Yes, I am aware about the security issues and I wanted to test only that. – Pulkit Jain Apr 13 at 11:10
  • You are welcome. Please mark the answer as accepted if it satisfied your question. That way other readers can take advantage. – SqlWorldWide Apr 13 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.