March 26, 2008 at 11:06 pm
Comments posted to this topic are about the item Job Execution System
March 27, 2008 at 4:10 am
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
March 27, 2008 at 6:59 am
Thank you. Excellent article and very useful information.
March 27, 2008 at 7:34 am
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!
March 27, 2008 at 10:03 am
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.
March 27, 2008 at 10:30 am
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...
March 27, 2008 at 9:56 pm
Good article with decent examples. Cheers!!!!:)
April 2, 2008 at 2:32 am
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
June 11, 2008 at 4:14 am
Nice article
August 20, 2008 at 11:15 am
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