25

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.

Edward Pescetto
  • 856
  • 1
  • 7
  • 16

5 Answers5

24

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

  1. In Object Explorer, expand a server.
  2. Expand SQL Server Agent.
  3. Expand Proxies, expand the subsystem node for the proxy, right-click the proxy you wish to modify, and click Properties.

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

Edward Pescetto
  • 856
  • 1
  • 7
  • 16
  • 7
    Copy-pasted from https://www.sqlservercentral.com/Forums/1349715/SQL-Agent-Job--Run-As-drop-down-list-is-empty Which "subsystem" do I need to expand? All I have is empty folders under "Proxies" – Alex from Jitbit Sep 09 '18 at 10:52
  • @Alex depends on how you are running your Job, for example if you are running with sqlcmd you would pick "Operation System" – ocean800 Jun 09 '20 at 21:44
10
  1. When editing the job step - switch to "Advanced" tab on the left, don't use the dropdown on the main page.
  2. Make sure the user is granted a role in the job database, even if he's a "sysadmin"

(the screenshot is for SSMS 17)

enter image description here

Alex from Jitbit
  • 53,710
  • 19
  • 160
  • 149
  • 1
    In SQL Server Management Studio 18(2019) there is no `run as` under advanced. – Emaborsa Apr 24 '20 at 06:09
  • @Emaborsa "Run as" is present (currently using SSMS v18.4). It is only visible if the step is a T-SQL step - and possibly only if the job owner is a sysadmin. See the sp_add_jobstep documentation for more info. – cbailiss Aug 25 '20 at 18:52
  • What would happen if the OP had kept the `Run as` value empty and it were an ssis package step? Assuming the job creator and job owner is not member of sysadmin, will the job run in context of agent service account? Or will the job not run? – variable Aug 19 '21 at 06:00
2

No, you shouldn't. Memebers of the roles you just mentioned can only create jobs that run as themselfes because they are non-administrative roles. If you want to run a job using antoher user, you'll need access to a proxy account. If I'm not mistaken, the only group that has access to create proxy accounts is sysadmin, so you'll need an admin to do that for you.

Members of the sysadmin role have permission to create job steps that do not specify a proxy, but instead run as the SQL Server Agent service account, which is the account that is used to start SQL Server Agent.

Diego
  • 34,802
  • 21
  • 91
  • 134
  • Thank you for answering Diego! I pasted a quote from MSDN in my question. The quote says I should be able to run jobs with proxy accounts even if I am not sysadmin. Are you saying that is not true? – Edward Pescetto Aug 24 '12 at 20:23
  • I asked my admin to explicitly grant access to the proxy on the principals page of the proxy properties. We will see if it works. – Edward Pescetto Aug 24 '12 at 20:35
  • It works. You can add access to proxy accounts. You have to explicitly grant access to roles or usernames on the principals page. – Edward Pescetto Aug 27 '12 at 13:54
  • For a ssis job step, if I am not a sysadmin user, suppose I leave the `Run as` as blank, then will the job step run in the context of agent service account? – variable Aug 19 '21 at 06:04
0

Create credentials first then you will be able to add it under proxies level. Once this is done then you can change from sql service agent to the credentials.

sar04x
  • 49
  • 1
  • 2
  • 7
0

I had this problem with SQL Server 2017 and I wanted to not have to create a whole SSIS package just to run some SQL with the proxy user.

My solution was using the SQLCMD utility and run the SQL Agent Job step as the CmdExec type instead of running it as T-SQL.

Then you could have it run something like:

sqlcmd -S <ComputerName>\<InstanceName> -Q "SELECT * FROM AdventureWorks2012.Person.Person"

https://learn.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-the-utility?view=sql-server-ver16

Kasper Olesen
  • 126
  • 1
  • 11