April 1, 2005 at 11:50 am
Hi,
I'd like to grant permssion to view SQL jobs in EM without granting sysadmin rights. Any idea how?
Thanks
Erico
April 1, 2005 at 12:00 pm
Hey,
There is a role in the msdb database called TargetServerRole. If you add the user to that database and to that role they should be able to view jobs. However, there are some other things that would need to be modified to allow them to view Job History, run status, and some other things as well. I hope this helps get you started, let me know if you need more info on the other items I listed.
April 1, 2005 at 2:55 pm
Thanks for the first part. What other this I need to do to allow user to job history and the alike..
Erico
April 4, 2005 at 9:33 am
To allow users to view job history, run status and other such things without giving the user sysadmin you will need to add some execute permissions to the user, or add them to the TagetServerRole. The ones which I added execute permission to are as follows:
USE master
GO
GRANT EXECUTE ON [dbo].[xp_readerrorlog] TO [public]
GO
USE msdb
GO
GRANT EXECUTE ON [dbo].[[sp_add_job] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_addtask] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_delete_jobschedule] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_delete_jobserver] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_delete_jobstep] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_droptask] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_post_msx_operation] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_purgehistory] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_reassigntask] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_start_job] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_stop_job] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_update_job] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_update_jobschedule] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_update_jobstep] TO [TargetServersRole]
go
GRANT EXECUTE ON [dbo].[sp_updatetask] TO [TargetServersRole]
go
Hope this helps, if you have any other questions just let me know.
April 4, 2005 at 10:01 am
I am not able to locate the TargetServersRole role in the msdb database. is this role user defined?
Please advise. Thanks.
April 4, 2005 at 10:48 am
It is under msdb...Roles. It is a standard db role called TargetServersRole. I know it is available in 7 and 2000, not sure abour 6.5.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply