November 4, 2009 at 12:47 pm
Hi,
I have sql server 2000 with SP4 and I am trying to give access to one of our users to start/stop jobs and for the same reason when the user is assigned to 'TargetServer Role' in msdb , user is getting an error(attached) when trying to expand jobs.
Following are the permissions for 'TargetServers' Role:
GO
DENY EXECUTE ON [dbo].[sp_delete_jobstep]
TO TargetServersRole
GRANT EXECUTEON [dbo].[sp_help_jobstep]
TO TargetServersRole
GRANT SELECT,UPDATE,DELETE ON [dbo].[sysdownloadlist]
TO TargetServersRole
DENY EXECUTEON [dbo].[sp_add_jobschedule]
TO TargetServersRole
GRANT SELECTON [dbo].[sysjobs]
TO TargetServersRole
DENY EXECUTEON [dbo].[sp_update_jobschedule]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_delete_jobschedule]
TO TargetServersRole
GRANTSELECT,UPDATE ON [dbo].[sysjobservers]
TO TargetServersRole
GRANTEXECUTEON [dbo].[sp_help_jobschedule]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_add_job]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_update_job]
TO TargetServersRole
GRANTSELECT,UPDATEON [dbo].[systargetservers]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_delete_job]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_start_job]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_stop_job]
TO TargetServersRole
GRANTEXECUTEON [dbo].[sp_sqlagent_refresh_job]
TO TargetServersRole
GRANTEXECUTEON [dbo].[sp_sqlagent_check_msx_version]
TO TargetServersRole
GRANTEXECUTEON [dbo].[sp_sqlagent_probe_msx]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_post_msx_operation]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_addtask]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_updatetask]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_droptask]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_add_jobstep]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_reassigntask]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_update_jobstep]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_purgehistory]
TO TargetServersRole
GRANTEXECUTEON [dbo].[sp_downloaded_row_limiter]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_add_jobserver]
TO TargetServersRole
DENYEXECUTEON [dbo].[sp_delete_jobserver]
TO TargetServersRole
GO
November 4, 2009 at 12:56 pm
- please post in the correct forum ! ( this is a sql2005 forum )
- the lack of SQLAgent manager role is one of the weak spots of SQL2000 !
- Keep in mind that many of the msdb job related sprocs perform their own permissions test ( jobowner or sysadmin)
If you can, make that owner jobowner, and you should be fine, but that user will be able to modify the job.
Another more dangerous way is to make that user(group) sysadmin :crazy:
To overcome this problem we did install some "application" servers with sql2000 standard edition, which is only to be used for its sqlagent job system.
Jobs are being started using a fixed schedule or using raiseallert !
The dba monitor this sqlserver instance, so no user databases are being created !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 4, 2009 at 1:02 pm
I cannot go with the following options:
If you can, make that owner jobowner, and you should be fine, but that user will be able to modify the job.
Another more dangerous way is to make that user(group) sysadmin
Any other way that this can be resolved??
November 4, 2009 at 1:28 pm
...
DENY EXECUTE ON [dbo].[sp_start_job]
TO TargetServersRole
DENY EXECUTE ON [dbo].[sp_stop_job]
TO TargetServersRole
...
You say that the users are trying to start and stop jobs, but according to the permission set you just posted about the server role they are in, it looks like they are being denied those permissions. If you grant those SPs to that server role, does it work for them then?
Joie Andrew
"Since 1982"
November 4, 2009 at 1:32 pm
I have tried that but was of no help...I also tried giving the grant permission in all the SP's for 'TragetServers Role', it didnt help either...I keep getting the same error....Not sure where the issue is.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply