InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 21 Mar 2010 08:06:28 GMT20http://www.sqlservercentral.com/Forums/Topic475221-427-1.aspxI too used your similar solution, but i do not want to allow my users to alter jobs, schedule them, delete them, etc. So i created the following SQLAgentLIMITEDOperatorRole in 2005. With this role, they can only start and stop jobs.USE [msdb]GO/****** Object: DatabaseRole [SQLAgentLimitedOperatorRole] Script Date: 06/24/2008 10:53:39 ******/CREATE ROLE [SQLAgentLimitedOperatorRole] AUTHORIZATION [dbo]GOEXEC sp_addrolemember N'SQLAgentOperatorRole', N'SQLAgentLimitedOperatorRole'GODENY EXECUTE ON sp_add_job to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_add_jobschedule to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_add_jobserver to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_add_jobstep to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_add_schedule to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_addtask to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_attach_schedule to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_delete_job to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_delete_jobschedule to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_delete_jobserver to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_delete_jobstep to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_delete_jobsteplog to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_delete_schedule to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_detach_schedule to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_droptask to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_maintplan_subplans_by_job to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_update_job to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_update_jobschedule to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_update_jobstep to [SQLAgentLimitedOperatorRole]DENY EXECUTE ON sp_purge_jobhistory to [SQLAgentLimitedOperatorRole]Wed, 20 Aug 2008 11:15:01 GMTJeremy Giacohttp://www.sqlservercentral.com/Forums/Topic475221-427-1.aspxNice articleWed, 11 Jun 2008 04:14:05 GMTBombardierhttp://www.sqlservercentral.com/Forums/Topic475221-427-1.aspxNice article.- I'd move the tables to msdb because they "belong" to the job system of your sqlserver.- Since security is your main concern, I'd avoid the use of "[i][b]grant all [/b][/i]..."Wed, 02 Apr 2008 02:32:41 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic475221-427-1.aspxGood article with decent examples. Cheers!!!!:)Thu, 27 Mar 2008 21:56:48 GMTAnirban Paulhttp://www.sqlservercentral.com/Forums/Topic475221-427-1.aspx[quote][b]Kenneth Wymore (3/27/2008)[/b][hr]Are the e-mail addresses in the script live? Or are they dummy addresses that you are just using as an example? I would hate to have those addresses recieve test e-mails from other people's systems if they were uncommented for any reason. Other than that, it's a great idea![/quote]yes, the email addresses are real... my bad. Please alter them to your specifications as needed. thanks for noticing that...Thu, 27 Mar 2008 10:30:45 GMTtjaybelthttp://www.sqlservercentral.com/Forums/Topic475221-427-1.aspxI like your solution. I had a similar problem and solved it a bit differently. I created a login to the server with permissions in msdb to execute jobs (SQLAgentOperatorRole), then added this login to a user database with a stored procedure that the user needing to execute the job had execute permissions on. Inside the stored procedure is the following: EXECUTE AS LOGIN = 'SQLAgentLogin' EXEC msdb.dbo.sp_start_job @pJobName REVERTThis allows the user to get instant feedback and isolates them from the job system.Thu, 27 Mar 2008 10:03:44 GMTjoshcsmith13http://www.sqlservercentral.com/Forums/Topic475221-427-1.aspxAre the e-mail addresses in the script live? Or are they dummy addresses that you are just using as an example? I would hate to have those addresses recieve test e-mails from other people's systems if they were uncommented for any reason. Other than that, it's a great idea!Thu, 27 Mar 2008 07:34:38 GMTKenneth Wymorehttp://www.sqlservercentral.com/Forums/Topic475221-427-1.aspxThank you. Excellent article and very useful information.Thu, 27 Mar 2008 06:59:58 GMTScott Abrantshttp://www.sqlservercentral.com/Forums/Topic475221-427-1.aspxThats a nice idea. My client uses something similar to trigger the distribution agent job for replication.I wonder though if this is a 2k5 server, why you would not just add these users logins to the SQLAgentOperatorRole, there by allowing them most SQL Agent priviledges they had before. I think there are a few things this role still can't do, but it would suffice for most things and they probably wouldn't even realise their priviledges had been taken away ;-)Thu, 27 Mar 2008 04:10:02 GMTFrank Bazanhttp://www.sqlservercentral.com/Forums/Topic475221-427-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Jobs/62374/">Job Execution System</A>[/B]Wed, 26 Mar 2008 23:06:29 GMTtjaybelt