July 18, 2003 at 12:14 pm
What is the best way to allow a group of users (3 or 4 users) to have rights to modify a group of jobs in Sql Server. Each user would need to be able to modify/monitor each job at one time or another because they back each other up. Even though they are trust worthy developers, I want to do this without making them System Administrators.
July 18, 2003 at 1:27 pm
Grant those users to access MSDB and add them to the member of TargetServerRole.
July 21, 2003 at 7:28 am
Thanks, That allowed them to see the jobs but not modify them. Any suggestions? I think I may create a database role in msdb to allow the correct rights. Has anyone ever tried this?
July 21, 2003 at 8:24 am
Are you sure?
July 21, 2003 at 9:28 am
quote:
Are you sure?
Yes, I'll try again though. Do the jobs need to be owned by a certain user or any user?
July 21, 2003 at 10:02 am
I created user TEST1 who created and owned job JOB1. I created another user TEST2 and granted it to access MSDB and added it to the member of TargetServerRole. Use TEST2 as login user going into EM and modified the job JOB1 owned by TEST1 without any problems. Is that what you try to achieve?
July 21, 2003 at 10:27 am
I'm getting "Error 229: Execute permission denied on object 'sp_update_jobstep' ..."
Are you using Sql Server 7 or 2000? I'm using Sql Server 2000. I noticed the permissions for the TargetServerRole are different from Sql 7 from Sql 2000. In particular in Sql 2000 they deny several rights that sql 7 grants rights to.
The system will allow me to modify the permissions for TargetServerRole but I don't know if I should. I don't know what this will affect.
July 21, 2003 at 10:31 am
What service pack is your SQL Server 2000 install?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
July 21, 2003 at 10:43 am
quote:
What service pack is your SQL Server 2000 install?K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
SP3a
Microsoft SQL Server 2000 - 8.00.760
July 21, 2003 at 11:08 am
I know there was a change in TargetServerRole as of SP3. I'll need to compare permissions between the SP2 and SP3 instances I have for testing. That might be the root cause of the problem.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
July 21, 2003 at 11:09 am
As a workaround, you could grant SQL Server logins (I know, I know) and have those SQL Server logins own the jobs. I don't like that solution but if you're pressed for time that may be the easiest way to get them up and running.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
July 21, 2003 at 11:52 am
quote:
I know there was a change in TargetServerRole as of SP3.
Brain is right. The test I have done is on service pack 2 but there are security change in server pack 3.
In service pack 3, job related stored procedures have been denied to TargetServerRole.
sp_add_jobTargetServersRoledboDeny
sp_add_jobscheduleTargetServersRoledboDeny
sp_add_jobserverTargetServersRoledboDeny
sp_add_jobstepTargetServersRoledboDeny
sp_addtaskTargetServersRoledboDeny
sp_delete_jobTargetServersRoledboDeny
sp_delete_jobscheduleTargetServersRoledboDeny
sp_delete_jobserverTargetServersRoledboDeny
sp_delete_jobstepTargetServersRoledboDeny
sp_downloaded_row_limiterTargetServersRoledboGrant
sp_droptaskTargetServersRoledboDeny
sp_help_jobscheduleTargetServersRoledboGrant
sp_help_jobstepTargetServersRoledboGrant
sp_post_msx_operationTargetServersRoledboDeny
sp_purgehistoryTargetServersRoledboDeny
sp_reassigntaskTargetServersRoledboDeny
sp_sqlagent_check_msx_versionTargetServersRoledboGrant
sp_sqlagent_probe_msxTargetServersRoledboGrant
sp_sqlagent_refresh_jobTargetServersRoledboGrant
sp_start_jobTargetServersRoledboDeny
sp_stop_jobTargetServersRoledboDeny
sp_update_jobTargetServersRoledboDeny
sp_update_jobscheduleTargetServersRoledboDeny
sp_update_jobstepTargetServersRoledboDeny
sp_updatetaskTargetServersRoledboDeny
sysdownloadlistTargetServersRoledboGrant
sysdownloadlistTargetServersRoledboGrant
sysdownloadlistTargetServersRoledboGrant
sysjobsTargetServersRoledboGrant
sysjobserversTargetServersRoledboGrant
sysjobserversTargetServersRoledboGrant
systargetserversTargetServersRoledboGrant
systargetserversTargetServersRoledboGrant
But in SP2, they are been granted.
sp_downloaded_row_limiterTargetServersRoledboGrant
sp_help_jobscheduleTargetServersRoledboGrant
sp_help_jobstepTargetServersRoledboGrant
sp_sqlagent_check_msx_versionTargetServersRoledboGrant
sp_sqlagent_probe_msxTargetServersRoledboGrant
sp_sqlagent_refresh_jobTargetServersRoledboGrant
sysdownloadlistTargetServersRoledboGrant
sysdownloadlistTargetServersRoledboGrant
sysdownloadlistTargetServersRoledboGrant
sysjobsTargetServersRoledboGrant
sysjobserversTargetServersRoledboGrant
sysjobserversTargetServersRoledboGrant
systargetserversTargetServersRoledboGrant
systargetserversTargetServersRoledboGrant
July 21, 2003 at 1:02 pm
Sql Server logins don't bother me. In fact, we run mixed because of our web applications. I can assign the application user name to the jobs. I was hoping not to because each application can have its own jobs and the same developers are administering them.
I've tried to create a database role in the msdb database with the correct permissions granted, similar to the sp2 version of TargetServerRole. However, I've been unsuccessfull in getting the user to see the job list. I'll keep trying.
July 21, 2003 at 1:18 pm
You need to have a look to view sysjobs_view.
CREATE 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)
It is called by those job stored procedures to decide who is able to view/change which jobs.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply