SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Job Execution System


Job Execution System

Author
Message
tjaybelt
tjaybelt
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 470
Comments posted to this topic are about the item Job Execution System



Frank Bazan
Frank Bazan
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2302 Visits: 1087
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
Scott Abrants
Scott Abrants
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 406
Thank you. Excellent article and very useful information.
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7907 Visits: 2418
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!
joshcsmith13
joshcsmith13
Default port
Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)

Group: General Forum Members
Points: 1433 Visits: 254
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.



tjaybelt
tjaybelt
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 470
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...



Anipaul
Anipaul
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11911 Visits: 1407
Good article with decent examples. Cheers!!!!Smile



ALZDBA
ALZDBA
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47941 Visits: 9084
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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Bombardier
Bombardier
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 135
Nice article
Jeremy Giaco
Jeremy Giaco
SSC Veteran
SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)SSC Veteran (263 reputation)

Group: General Forum Members
Points: 263 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]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search