October 10, 2005 at 11:07 am
I am looking for a way to create a role that can administer all SQL Agent jobs from Enterprise Manager, without being SA on the box.
I have experimented with the TargetServersRole in msdb, and with a hack to msdb.dbo.sp_get_composite_job_info (SELECT @is_sysadmin = 1--ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) and assigning permissions to start and stop jobs etc, I get the desired results. However, when I copy the permissions to a role of my own creation, access to jobs fails. There appears to be some "hidden" permission given to the TargetServersRole in msdb.
Anyone out there have any luck creating a job manager role and willing to share?
Thanks in advance.
Terry Duffy
October 11, 2005 at 3:56 am
You will certainly need to modify some Microsoft stored procedures to do what you want to achieve.
I looked at doing something similar, but my site is not prepared to modify the MS procs so we did not implement anything. However, I found no problems in creating a new database role for the job administration, and preferred that approach as it would have made us independant of any MS changes to TargetServersRole. If you plan to use the TSR role, I think you have to revoke some DENY privileges on some SPs. If you use your own role you are just granting access.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 11, 2005 at 11:48 am
WOuld it be possible to post the 'explicit' grants mentioned for creating the user defined role please ... I've been looking for something like this for some time - just never had the time to hack it out ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 11, 2005 at 11:58 am
In addition to changing the stored procedure msdb.dbo.sp_get_composite_job_info as shown in my first post, the below permissions are needed. There are only three changes to the base role permissions.
Be aware that I have not been able to get a role of my creation to work (the reason for my post), but with the changes below TargetServerRole has full ability to start, stop, and view all jobs.
Also note that the TargetServerRole does not behave like other roles. The below code should work but it does not. You will receive an error that "There is no such user or group 'TargetServerRole'" so use the GUI to edit the role's permissions.
GRANT SELECT,UPDATE ON [dbo].[systargetservers] TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_post_msx_operation]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_add_jobstep]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_update_jobstep]TO TargetServerRole
GRANT EXECUTE ON [dbo].[sp_downloaded_row_limiter] TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_add_jobserver]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_delete_jobserver]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_delete_jobstep]TO TargetServerRole
GRANT EXECUTE ON [dbo].[sp_help_jobstep] TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_add_jobschedule]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_update_jobschedule]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_delete_jobschedule]TO TargetServerRole
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_add_job]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_update_job]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_delete_job]TO TargetServerRole
GRANT EXECUTE ON [dbo].[sp_get_composite_job_info] TO TargetServerRole --new permission
GRANT EXECUTE ON [dbo].[sp_start_job] TO TargetServerRole --changed from deny to grant
GRANT EXECUTE ON [dbo].[sp_stop_job] TO TargetServerRole --changed from deny to grant
GRANT EXECUTE ON [dbo].[sp_sqlagent_refresh_job] TO TargetServerRole
GRANT EXECUTE ON [dbo].[sp_sqlagent_check_msx_version] TO TargetServerRole
GRANT EXECUTE ON [dbo].[sp_sqlagent_probe_msx] TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_addtask]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_updatetask]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_droptask]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_reassigntask]TO TargetServerRole
DENY EXECUTE ON [dbo].[sp_purgehistory]TO TargetServerRole
GRANT SELECT,UPDATE,DELETE ON [dbo].[sysdownloadlist] TO TargetServerRole
GRANT SELECT ON [dbo].[sysjobs] TO TargetServerRole
GRANT SELECT,UPDATE ON [dbo].[sysjobservers] TO TargetServerRole
Terry
October 11, 2005 at 12:10 pm
One more thing. If you choose not to hack the system procedure (and many ppl frown on this), your role will not be able to see the current status of a job. Other than that the role can still administer, but not change jobs or job steps.
Terry
October 11, 2005 at 12:59 pm
Thanks a ton Terry ! I'll let you know if I can change anything for the better !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 7, 2006 at 1:00 pm
Rudy ... Did you come up with a good solution ? I'm in the same situation. TIA
TDuffy ... I'm trying your approach and noticed you have a typo. If you grant acces to TargetServersRole then the command works. Note the 'S' at the end of ServerS
November 7, 2006 at 1:06 pm
Got really side-tracked ... maybeI'll get back to it in another week or two ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 14, 2008 at 2:26 pm
Hi guys
I had the same problem. The database roles created by me didn't work...
To fix this problem you have to change a view: msdb.dbo.jobs_view including your new role. Here the original code of the view:
ALTER VIEW sysjobs_view
AS
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
I hope this helps
May 14, 2008 at 2:28 pm
I forgot to send the code changed 🙂
ALTER VIEW sysjobs_view
AS
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'MyNewDatabaseRole'), 0) = 1)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply