SQL Agent Jobs (Serious Problem with Jobs) Please advice
-
Hi All,
I was wondering whether there was a way in SQL 2005 in which ordinary users can run SQL agent jobs without having SA rights, and also I have tried making the users the owners of the jobs, but that causes another problem, which are permission issues and also schema issues as well.
I have made users part of the SQL agent operator role, but as I understand this can only allow users view jobs etc, but not actually run them.
Thanks
-
John,
if it's just the starting of jobs you're users need, you can give them execute permissions for the sp_start_job procedure in msdb.
Markus
[font="Verdana"]Markus Bohse[/font]
-
Hi
Thanks for the reply.
Problem is that they might need other things as well, at the moment the job activity monitor doesnt even work, i.e it doesnt show the status of jobs, except if they are "SA".
They might also need other things like change job schedule etc, change the code if necessary.
Problem is that they can own the job, job owners can only be an individual user, so if one user owns the job, it means other non-sa users cannot do anything with the job. It also causes other problems.
Is there a way to sort this out, as I had allow developers change job schedules, code, etc as it saves me time having to do such petty jobs.
Thanks
-
John,
See "SQL Server Agent Fixed Database Roles", particularly SQLAgentOperatorRole, in BOL.
Greg
Greg
-
I see what you mean. It looks like you can't even grant other users or roles permission to execute stored procedures like sp_add_job and sp_delete_job anymore like you could in SQL 2000.
You may be stuck with administering jobs unless you want to make someone a member of sysadmin. Sorry.
Greg
Greg
-
Not sure why you said that. I just created a test login assingned it to the SQLAgentOperatorRole and the login *can* run SA owned jobs.
Which service pack and Edition are you on ?
* Noel -
John,
Could you list the things you want a non-sysadmin to be able to do with jobs?
Greg
Greg
-
According to BOL:
Only a job owner or members of sysadmin can edit jobs.
Only a job owner or members of sysadmin can change a schedule, though a member of SQLAgentOperatorRole (with sp_update_schedule) can enable/disable schedules it doesn't own.
A job owner, members of sysadmin and SQLAgentOperatorRole (with sp_update_job) can enable/diable jobs.
I haven't found a way around those restrictions.
Greg
Greg
-
Create a sql login.
Make the owner of the jobs that need to be editied by developers the sql login.
If someone needs to go in an edit a job or make other changes make them login with the sql login. The only other issue is that jobs not owned by the SA account don't get written to the log in C
rogramfiles... so debugging a job could be a problem. -WM
-
Have you tried using the Database roles under MSDB ??
-
here's a method I've used on SQL 2000 (although I haven't tested it in sql2005- it does apply ok)
Hope it helps
use msdb
revoke exec on sp_start_job to TargetServersRole
deny exec on [sp_add_job] to TargetServersRole
deny exec on [sp_add_jobschedule] to TargetServersRole
deny exec on [sp_add_jobserver] to TargetServersRole
deny exec on [sp_add_jobstep] to TargetServersRole
deny exec on [sp_addtask] to TargetServersRole
deny exec on [sp_delete_job] to TargetServersRole
deny exec on [sp_delete_jobschedule] to TargetServersRole
deny exec on [sp_delete_jobserver] to TargetServersRole
deny exec on [sp_delete_jobstep] to TargetServersRole
deny exec on [sp_droptask] to TargetServersRole
deny exec on [sp_post_msx_operation] to TargetServersRole
deny exec on [sp_purgehistory] to TargetServersRole
deny exec on [sp_reassigntask] to TargetServersRole
deny exec on [sp_stop_job] to TargetServersRole
deny exec on [sp_update_job] to TargetServersRole
deny exec on [sp_update_jobschedule] to TargetServersRole
deny exec on [sp_update_jobstep] to TargetServersRole
deny exec on [sp_updatetask] to TargetServersRole
exec sp_addrole '_SQL_MSDB_VIEW_JOB'
exec sp_addrole '_SQL_MSDB_START_JOB'
deny exec on sp_start_job to _SQL_MSDB_VIEW_JOB
grant exec on sp_start_job to _SQL_MSDB_START_JOB
exec sp_addrolemember TargetServersRole,_SQL_MSDB_VIEW_JOB
exec sp_addrolemember TargetServersRole,_SQL_MSDB_START_JOB
sp_addlogin 'bpflanzer','xxxxxx'
exec sp_grantdbaccess 'bpflanzer'
exec sp_addrolemember _SQL_MSDB_START_JOB,'bpflanzer'
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply