Job Execution System
-
Comments posted to this topic are about the item Job Execution System
-
Thats 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
Kindest Regards,
Frank Bazan
-
Thank you. Excellent article and very useful information.
-
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!
-
I 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
REVERT
This allows the user to get instant feedback and isolates them from the job system.
-
Kenneth Wymore (3/27/2008)
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!yes, the email addresses are real... my bad. Please alter them to your specifications as needed.
thanks for noticing that...
-
Good article with decent examples. Cheers!!!!:)
-
Nice 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 "grant all ..."
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
-
Nice article
-
I 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]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'SQLAgentLimitedOperatorRole'
GO
DENY 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]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply