Why is the "Run As" drop down list is always empty when I try to set up a SQL Agent Job? I am trying to set up some SQL Agent Jobs to run using a proxy account. I am a member of the SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole. When I try to add a step to to the job, I select SQL Integration Services Package and the Run As drop down list is empty.
Anyone who is a sysadmin can view the proxy. Shouldn't I be able to use the proxy as a member of SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole? What am I missing here?
(The proxy account is active to the subsystem: SQL Integration Service Packages and this is SQL Server 2008 R2)
EDIT -
MSDN: "Members of these database roles (SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole) can view and execute jobs that they own, and create job steps that run as an existing proxy account." And this other article on fixed server roles mentions that access can be granted to proxies, but it does not mention how to do it: MSDN.
By default, the SQL Agent runs with the SQLSERVERAGENT account.
Similar to Windows services, SQL Agent Jobs run under a user or service account configured in the job. Job failures can occur when there are permission or authentication issues with the user or service account. Common issues include: Account expired.
I found the answer to this. Users who are not sysadmin have to have access to the proxy account explicitly granted to their role or username:
To grant access to proxy accounts for non-sysadmins
On the General page, you can change the proxy account name, credential, or the subsystem it uses. On the Principals page, you can add or remove logins or roles to grant or remove access to the proxy account.
http://msdn.microsoft.com/en-us/library/ms187890(v=sql.100).aspx
(the screenshot is for SSMS 17)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With