permission trouble running jobs

Question

permission trouble running jobs

jay-h

SSCoach

Points: 18816
More actions
July 7, 2005 at 1:56 pm

#86750

I have one server (sql 2000 on W2k server) which has the following problems:
*No users other than system administrators can view, run or look at jobs.
*I created a user and tried each of the other predetermined admin roles, and it failed for every one of them, but would run/view jobs if I made it a system admin.
*When trying to look at the listing of jobs the following error occurs: "error 229 execute permission denied on xp_sqlagent_enum_jobs". Poking around in different ways also generates similar errors for "xp_sqlagent_param" and "Xp_instance_regnumvalues". I have checked these sprocs for deny permissions and cannot find anything wrong, explicitly giving the user execute permissions does not help.
This is causing problems because certain users are supposed to control jobs in their databases.

Jay

...
-- FORTRAN manual for Xerox Computers --

Viewing 6 posts - 1 through 5 (of 5 total)

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

Azzam Alkadi SSChasing Mays Points: 640 More actions · Answer 1

Only sa can manage jobs for all of the SQL server. However, other users who have been given access to MSDB can create their own jobs and manage them. The problem here is not just access to MSDB but master too. It would involve a lot of researching to find out which sprocs you need to give access to users other than sa.

jay-h SSCoach Points: 18816 More actions · Answer 2

I should clarify that users cannot even manage their own jobs. They get errors just trying to create a job, actually they get errors just trying to open the jobs list (even if it is empty of their jobs).

...

-- FORTRAN manual for Xerox Computers --

Perry Citrowske Ten Centuries Points: 1243 More actions · Answer 3

I had a similar situation with a developer of mine who needed to be able to restart jobs etc. Now, I have enough trust in him to allow him to see all scheduled jobs. So, take a look at what I did and see how it can apply to your situation.

I put the windows user in the MSDB database with the role "targetserver". Also had to remove the "deny permission" checkbox to the sp "sp_start_job", and add his user "execute" permission. Same thing with "sp_stop_job". I also had to figure out how to change dts owner so that he could modify the dts packages. I found the following stored procedure on the Internet which reassigned all users from one user to another. EXEC msdb.dbo.sp_DTSReplaceOwner @old_owner='****', @new_owner='***' I found it at: http://www.databasejournal.com/features/mssql/article.php/1461511 The other stored procedure I found there was one that simply reassigned one owner to the next one dts package at a time. sp_reassign_dtspackageowner [@name =] 'name', [@id =] 'id', [@newloginname =] 'newloginname'

Mathy SSC Rookie Points: 26 More actions · Answer 4

helped me a lot.

I had a situation where the dts package owner left from the company .

DBA removed his account.

so last 3 days the job was not running and i searched and found a really helpfull tip from you.

many thanks

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 5

Scheduled jobs are executed under the same user under which SQLServerAgent service runs.