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)
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.
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.