Non-SysAdmins have been denied permission to run CmdExec job steps

Someone has asked me to schedule a SQL job on a SQL 2000 Server.  The job simple calls an executable program.  She has also requested to be able to monitor the job using the “Job Activity Monitor” in the SQL Server 2008 Management Studio  installed on her machine.  She is using a SQL account which has limited access to the SQL 2000 Server (the account is DBO of a couple of databases but not SysAdmin). 

Now according to How to: Open Activity Monitor (SQL Server Management Studio), the SQL account she is using must have SELECT permission to the sysprocesses and syslocks tables in the master database in order for her to use the “Job Activity Monitor” to monitor the job.   Permission to view these tables is granted by default to the public database role.  So I grant the SQL account the public database role but she can’t see any job in the “Job Activity Monitor”.

Then I figured that the SQL account needs more permission than what are mentioned in How to: Open Activity Monitor (SQL Server Management Studio).  The SQL account needs to have either the SysAdmin role or needs to be the owner of the SQL job.  There are also suggestion to create custom stored procedure to monitor the job but it is not what she wants.  Giving the SQL account the SysAdmin role is not an option so I have to make the SQL account the owner of the job.

By making the SQL account the owner of the SQL job, she is able to see the job using “Job Activity Monitor”.  The next problem is the job failed to complete with the error “Non-SysAdmins have been denied permission to run CmdExec job steps.  The step failed.”.

The SQL Server Agent service was configured to run using a domain account.  This account has administrator rights on the server and has been given the SysAdmin role on the SQL 2000 Server.  Now the problem is when owner of the job does not have the SysAdmin role, the SQL job will run using the account of the job’s owner.  In other words, the job executes using the SQL account instead of the SQL Server Agent’s service account.  The workaround is to set up a proxy account with SysAdmin role to execute the step which requires SysAdmin rights (in this case, the step is to run CmdExec).  The proxy account can be setup by going to the SQL Server Agent Properties and click on the Job System tab.

Uncheck the “Only users with SysAdmin privileges can execure CmdExec and ActiveScripting job steps” checkbox in the “Non-SysAdmin job step proxy account” group.  You will be asked to enter a domain account for the SQL Agent proxy account.

After setting up, the “Only users with SysAdmin privileges can execure CmdExec and ActiveScripting job steps” checkbox will remain uncheck.

Just when I thought I have it all sorted out, the job still failed but with a different error.

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

This error was caused by the missing “Replace a process level token” rights.  The proxy account needs to have the “Replace a process level token” rights and this can be done by using the “Local Security Settings” console.

Double click on the "“Replace a process level token” policy and add the proxy account.  I have to restart the SQL Server Agent service to have the new rights to take effect.

That solved the job execution problem and she is able to monitor the job from her machine through “Job Activity Monitor”.