Job Execution System

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by tjaybelt » Job Execution System

Job Execution System

Rate Topic

Display Mode

Topic Options

Author

Message

tjaybelt

Posted Wednesday, March 26, 2008 11:06 PM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:48 PM
Points: 604, Visits: 381

Comments posted to this topic are about the item Job Execution System

Post #475221

Frank Bazan

Posted Thursday, March 27, 2008 4:10 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 3:02 PM
Points: 268, Visits: 1,055

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 Wink

Kindest Regards,

Frank Bazan
@Bikeride2Africa

Post #475293

Scott Abrants

Posted Thursday, March 27, 2008 6:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:13 PM
Points: 43, Visits: 306

Thank you. Excellent article and very useful information.

Post #475362

KWymore

Posted Thursday, March 27, 2008 7:34 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:57 PM
Points: 2,575, Visits: 1,533

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!

Post #475395

joshcsmith13

Posted Thursday, March 27, 2008 10:03 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:57 AM
Points: 502, Visits: 212

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.

Post #475542

tjaybelt

Posted Thursday, March 27, 2008 10:30 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:48 PM
Points: 604, Visits: 381

Kenneth Wymore (3/27/2008)

yes, the email addresses are real... my bad. Please alter them to your specifications as needed.
thanks for noticing that...

Post #475567

Anipaul

Posted Thursday, March 27, 2008 9:56 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 1:35 AM
Points: 4,789, Visits: 1,336

Good article with decent examples. Cheers!!!!:)

Post #475869

ALZDBA

Posted Wednesday, April 02, 2008 2:32 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #478291

Bombardier

Posted Wednesday, June 11, 2008 4:14 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 05, 2010 1:12 AM
Points: 14, Visits: 135

Nice article

Post #515040

Jeremy Giaco

Posted Wednesday, August 20, 2008 11:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 04, 2009 8:25 AM
Points: 159, Visits: 122

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]

Post #555977

« Prev Topic | Next Topic »

Permissions