Cannot start a sql server Job

Question

Cannot start a sql server Job

Trooper09

SSCrazy

Points: 2047
More actions
October 5, 2009 at 9:40 am

#142276

Hi,
I have a situation where the user whos does not have SA rights cannot start the sql server job. The user has necessary rights (owner on Master,msdb databases , assigned to SQl server Agent operator,reader,user roles and target serverroles) when the users tries to start the job the folllowing error appears:
The execute permission was denied on object 'sp_start_job' database msdb , schema 'dbo'.
I have tried giving execute permission on all the SP on msdb but doesnt seem to work.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

Hi, you have to add user to one of the roles defined in the msdb, as per BOL:

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

SQLAgentUserRole

SQLAgentReaderRole

SQLAgentOperatorRole

HTH

Piotr

...and your only reply is slàinte mhath

Trooper09 SSCrazy Points: 2047 More actions · Answer 2

The user is already a member of the listed roles.

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736479

October 5, 2009 at 12:10 pm

#1061919

What other rights? Perhaps something that deny's access?

Trooper09 SSCrazy Points: 2047 More actions · Answer 4

i have tried to script the user rights the login has and couldnt find anything with DENY ...so I am pretty sure that there are no Deny access for the login...

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 5

Maybe you could try to create a user without login, assign it to roles change execution context to it and try to exec the sp_start_job?

As in:

use msdb

go

create user utest without login

go

--it works only when utest is a member of SQLAgentOperatorRole

exec sp_addrolemember 'SQLAgentOperatorRole', 'utest'

--these two roles don't let execute sp_start_job

exec sp_addrolemember 'SQLAgentReaderRole', 'utest'

exec sp_addrolemember 'SQLAgentUserRole', 'utest'

go

execute as user='utest'

go

sp_start_job 'TestJob'

go

revert

go

drop user utest

If this works for you, verify if your user is member of SQLAgentOperatorRole for sure. I am sorry if I suggest thing you checked million times by now.

Regards

Piotr

...and your only reply is slàinte mhath

Trooper09 SSCrazy Points: 2047 More actions · Answer 6

I have tried to duplicate the issue in the Test environment and the user was able to Start the job and schedule it. I am not sure why the user on Prod environment with the similar rights has the problem. The only difference is that the prod login is using Windows Authentication and I tried SQL server login on test.....and this works.

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 7

How the user is executing this proc? What type of application is used?

Regards

Piotr

...and your only reply is slàinte mhath

Trooper09 SSCrazy Points: 2047 More actions · Answer 8

Here is the thing. The user had the following roles assigned on msdb

@rolename = 'db_dtsadmin',

@rolename = 'db_dtsltduser',

@rolename = 'db_dtsoperator',

@rolename = 'db_owner',

@rolename = 'SQLAgentOperatorRole',

@rolename = 'TargetServersRole',

when I changed it to the following roles only , it worked

@rolename = 'db_dtsadmin',

@rolename = 'db_owner',

@rolename = 'SQLAgentOperatorRole'

That is strange because the user had more than required rights earlier and was for some reason not working. May be had DENY set the other roles .

But here is the new problem , the user cannot schedule the Job.

I have assigned the user to the role @rolename = 'TargetServersRole', and now they get the following error:

The execute permission was denied on the object 'sp_add_jobschedule', database 'msdb' schema 'dbo'. the user is the db_owner on the database.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 9

Try dropping/recreating the same login with same rights. I presume somewhere a explicit DENY is holding you back.

MJ

Trooper09 SSCrazy Points: 2047 More actions · Answer 10

I tried that ..but was of no help....anything else that I am missing..

Trooper09 SSCrazy Points: 2047 More actions · Answer 11

Now I get a meesage saying that

The schedule was not attached to the specific job.The schedule owner and the Job owner must be the same or the operation must be perfomed by a sysadmin.....

I dont want to make the user the job owner or sysadmin...so How do I deal with it?

Trooper09 SSCrazy Points: 2047 More actions · Answer 12

One more thing ..when the user is assigned to the role 'TargetServersRole ' , he is not able to start /stop any job. I did look into permissions for the role but nothing was specified there...

is there any other way to find out whether the role has any deny permissions..??

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 13

That's rather strange. Does this occur with any other users on other machines in your domain? Is the job a target server job?

Trooper09 SSCrazy Points: 2047 More actions · Answer 14

Oh boy....finally.....The role 'TargetServerRole' on msdb had DENY access on the Stored procedures which will be used to start/stop/create a new schedule/change a schedule for the jobs.. once the GRANT permissions were given it fixed the issue.

Thank you all guys.