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

About these ads

17 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!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 228 other followers

%d bloggers like this: