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');
Before I answer your question I want to draw your attention about granting permissions to Public Msdb Role on the Proxy.
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