Originally posted on MSSQLFUN:

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…

View original 30 more words

About these ads

1 Comment

  1. lista de emails says:

    i always share your sites post with my friends. keep posting and i will follow you…

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 210 other followers

%d bloggers like this: