Home » CodeProject » The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’.

The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’.

Issue : Today I have read one issue over one forum, One user is having below 3 DB roles on MSDB but whenever user try to run SQL Agent job, it get below error message.

We have Checked that SQL Agent job related DB role is properly given to user. Also, Job is working fine by the use is having sysadmin roles.

· SQLAgentUserRole

· SQLAgentReaderRole

· SQLAgentOperatorRole

Resolution : We have found that someone has deny the execute permissions from SQLAgentUserRole over sp_start_job store procedure in MSDB.

We have Run the below query to check the permissions over sp_start_job store procedure in MSDB.

USE MSDB

GO

SELECT PR.NAME, DP.PERMISSION_NAME, DP.STATE_DESC

FROM MSDB.SYS.DATABASE_PERMISSIONS DP

JOIN MSDB.SYS.OBJECTS O ON DP.MAJOR_ID = O.OBJECT_ID

JOIN MSDB.SYS.DATABASE_PRINCIPALS PR

ON DP.GRANTEE_PRINCIPAL_ID = PR.PRINCIPAL_ID

WHERE O.NAME = ‘SP_START_JOB’

Found that someone has deny the execute permissions from SQLAgentUserRole over sp_start_job store procedure in MSDB.

By running the below query, execute permission has been given back & issue has been resolved.

USE MSDB

GO

GRANT EXECUTE ON SP_START_JOB TO SQLAGENTUSERROLE

Reference : Rohit Garg (http://mssqlfun.com/)


23 Comments

  1. lista de email says:

    i must say the links are very useful.

  2. hello It’s a nice post.

  3. lista de email says:

    helpful! i love reading your articles, thanks for all.

  4. lista de emails says:

    i really appreciate your working style writing style of the article is really exceptional.

  5. lista de email says:

    your website is so organized, and your posts are always common sense, congratulations for making the difference.

  6. lista de emails says:

    your website looks like an encyclopaedia that teaches us several things.

  7. lista de email says:

    very nice post. good stuff.

  8. Amedar Consulting Group says:

    Blog…

    I will right away snatch your rss feed as I can’t in finding your e-mail subscription hyperlink or e-newsletter service. Do you’ve any? Please permit me recognise so that I could subscribe. Thanks….

  9. lista de emails says:

    your content made me want to read more and more. congratulations for the work. thanks!

  10. lista de emails says:

    thanks for the info, appreciated it. it was so well formatted.

  11. Shaik Mansoor says:

    In my case, it was TargetServerRole that was denied the execute access as the job was dealt with dtsx package. It worked fine after granting exec access to TargerServerRole.

  12. libres gratis says:

    Hello, i think that i ѕaw you visited my blog so i came
    too “return the favor”.I am attempting to find things to eոhancе my sitе!I
    suppose its okk to use some of your іdeas!!

  13. ramya says:

    Hi Guys,

    I’m just trying to execute the store procedure ‘sp_start_job’ using C# vb.net, and i’m getting execution denied error in local whereas i could execute this from process account.

    Please suggest me to clear this problem…

    Thanks in Advance!!!

  14. Developers Austin says:

    It’s awesome in support of me to have a web page, which is
    valuable in support of my experience. thanks admin

  15. Israel Greß says:

    Thanks a lot!!!! That’s exactly what I was looking for… Greetings from Mexico

  16. dingnv says:

    Awesome article!!! Save my life
    Thank genius!!

  17. MAYUR CHALLAWAR says:

    tHIS WAS EXTREMELY HELPFUL FOR ME. tHANKS A LOT.

Leave a Reply to Israel Greß Cancel reply

Join us on Facebook

microsoftcommunitycontributor
%d bloggers like this: