Problem with job permissions

SO here's the deal - I have a developer that needs to be able to schedule jobs, but that I do NOT want to give System admin privelges to. She has DBO privelges on the DBs she needs, and is a member of Bulk Insert and Process ADmin server roles. She has the abiltiy to schedule jobs but if she creates one that uses CmdExe steps they fail with the message:

Executed as user: MCC_GENESIS\SQLSrv. The process could not be created for step 1 of job 0xB793D66AE8016647AC64FFEB3EE2E202 (reason: A required privilege is not held by the client). The step failed.

I have configured the SQL Agent to use a proxy account to run scripts if the user is not SysAdmin, and I am using the same domian account that starts the MSSQL service as the proxy. The account is a DOMAIN admin (I know bad idea, but that's what they gave me to work with.)

So here is the question - why do jobs fail when they are owned by the debveloper, but run when owned by SA - both are using the same account, (SA using account that starts server, develped using it thorugh proxy)

Any advie woudl be greatly appreciatedDid you reset the SQLAgentCmdExec permissions ?|||Originally posted by rnealejr
Did you reset the SQLAgentCmdExec permissions ?

I actually found the answer on anotehr forum, I had to grant the user permisson to replace a process level token. Once I did that, everythign worked fine.

Thanks for the reply :)|||Would you mind posting the solution - for future reference ?|||The solution was just that - granting the NTUser ID permission to replace proces level tokens - this can be done with policies or local system permissions. I think by default this is granted already, but the organization I am consulting for had it locked down. Once that was done, non- SA users could use CmdExe steps in their jobs.|||I was asked by another user to post the solution:

You first have to set up a Proxy account for running scripts as a non-sysadmin,

1. Open Enterprise manager.
2. Click on the server name -> Management
3. Right Click on SQL Server Agent
4. Choose the Job System tab
5. Uncheck the box that says "Only users with sysadmin privleges can execute CMdExec and ActiveScripting job steps.
6. You will be prompted to enter log in information - enter the system account info you want to use (Domain or Local) to run scripts - it should be at least a local admin account for the box, I normally use the same logon as that used by the MSSQL service.

then Verify/Change permissions.

1. Open Administrative tools (You will need admin rights on the server to do this)
2. Go to Local Security Policy ( this can also be done at the domain level, speak to your LAN admin about how they would like this setup)
3. Click on "user rights Assignment" in the left hand pane,
4. In the right hand pane, scroll down until you find " replace a process level token".
5. Double click on "Replace a Process level token", and add the proxy user account (Step 6 above).
6. Apply the change. You may have to restart the MSSQL and SQL Server Agent services for the change to take place, I am not sure.|||Originally posted by cdols
These are the exact steps I'm using to setup the Proxy account however, when I attempt to enter the login information for the Proxy I get the following error: "Unable to set the SQL Agent proxy account because of the reason listed below. 'Error executing extended stored procedure: Specified user can not login' "

What's strange is that this is the same account that the Server & Server Agent services use (and they are working fine). This domain account has also been added to SQL as a login with system admin privileges. The account has also been made a local administrator on the server.

In terms of testing the account, I can connect successfully to SQL with Query Analyzer using this domain account.

I've actually reloaded the OS & reinstalled SQL2000 and still have the problem (OS is 2000 Server and SQL2K is SP3).

What I have found for solutions so far assume that you actually CAN set the Proxy account.

Anyone have an idea what's going on?

Doug|||Hi,

Was you able to find the solution?

Thanks,
Leosql

Problem with DTS schedule

Hi,

I have a little DTS problem here.
This package is programmed in an SQL Server v7.0 box and it executes OK when I run it manually.

The problem starts when I want to schedule the automatic execution of the DTS and in the Job History I got this error: "Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed."

That means that the user owner of the DTS must be a SysAdmin? The current role for the user is "Process Administrator" but not "System Administrator".

As I cannot give the user superpower in the server, do I have to recompile de DTS under other user that is System Administrator?

Regards,

GOne of the archived post refers
Expand Management, right-click SQL Server Agent, and then click Properties. Click the Job System tab.

Under Non-SysAdmin job step proxy account, clear the Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps check box, and click Reset Proxy Account.

Type the user name, password, and domain of the user account to be used by SQL Server Agent when running jobs owned by users who are not system administrators.