October 26, 2006 at 3:49 am
Hi All, hope you can help - I am getting the following error when attempting to run a job through SQLAgent Proxy in SQL 2005:
"EXECUTE permission denied on object 'sp_start_job', database 'msdb', schema 'dbo'."
Steps I took to create:
1. Created a Credential 'SQLProxy_Credential' using a pre existing domain account (DOM\SQLProxy)
2. Created a Proxy 'SQLProxy' using the credential from above.
Gave the following Activate permissions for the proxy: ActiveX,OS(CmdExec),SSIS packages
3. Created a local test windows account on server 'TestSQLProxy'
Added to SQL Server with following Access to msdb:
public
SQLAgentOperatorRole
Also public access to master
-- Added as a principal to the proxy 'SQLProxy'
4. Created a test job as a sysadmin user eg. BOB
5. Tested job as BOB - worked fine
6. Logged on as TestSQLProxy & tried to run job resulting in error above.
...have I missed something(s) fundamental?
Many Thanks in advance
October 30, 2006 at 8:00 am
This was removed by the editor as SPAM
September 6, 2007 at 10:23 am
Dear friend,
add de user that you are using for execute the job to the msdb database , add it to the targetServerRole role in the msdb database
September 7, 2007 at 6:37 am
What type of proxy did you create? SQL 2005 has lots of different proxies and you need to create the right one. Also, you only need to create proxies if the account running the process is NOT a sysadmin.
If you are running job steps under the CmdExec subsystem in SQL Agent, you need to set up a proxy for CmdExec.
In order to provide a container for all authorities needed by a non-sysadmin job, I recommend that each job owner account that is not a sysadmin is set up as its own proxy. This allows you to have both SQL permissions and file permissions owned by the same account.
1) You need to set up a credential for the job owner account (see BOL for details). I suggest you use the job owner account name as the credential name.
2) You need to define a CmdExec proxy in SQL Agent, using the credential you just created. Again, use the job owner account name as the proxy name.
3) You need to set up rights for the proxy. Set the Principal Type to SQL Login and map it to the job owner account.
4) You now grant the job owner account the ability to manage jobs. This is also needed to allow the proxy account to work. Grant access to the SQLAgentUserRole in msdb to the login. See BOL to see if you want to grant other rights.
5) You can now associate the proxy with the required job steps. Edit the job step and set 'Run As' to your proxy.
Everything should now run OK. If you want to add other Agent subsystems to the proxy (e.g. SSIS) then do so. However, be aware that any code in your job that runs xp_cmdshell will run using the authority of the global xp_cmdshell proxy - see BOL on details of how to set this up. All other SQL code and file accesses will run using the authority of the job owner account.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 11, 2011 at 3:54 pm
The solution really helped me here to allow one specific user to run job manually.. Previously it was giving error
''The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)''
to this user on running job manually but this job was running fine on its schedule as owner was set as SA.
Did as below:-
1) Went through MSDB-- Security --Users -- UserName -- User Name Properties -- Assigned the 'TargetServerRole' in msdb and then granted Execute permission to concern user to SP_Start_Job and SP_Stop_Job and it resolved the issue.
Thank You.
Surendra Patle..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply