Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Job Execution System Expand / Collapse
Author
Message
Posted Wednesday, March 26, 2008 11:06 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, December 11, 2014 4:57 PM
Points: 615, Visits: 447
Comments posted to this topic are about the item Job Execution System


Post #475221
Posted Thursday, March 27, 2008 4:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:15 AM
Points: 268, Visits: 1,074
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
Post #475293
Posted Thursday, March 27, 2008 6:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 12:55 PM
Points: 46, Visits: 343
Thank you. Excellent article and very useful information.
Post #475362
Posted Thursday, March 27, 2008 7:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:42 AM
Points: 3,429, Visits: 2,039
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
Posted Thursday, March 27, 2008 10:03 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 11:39 AM
Points: 509, Visits: 246
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
Posted Thursday, March 27, 2008 10:30 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, December 11, 2014 4:57 PM
Points: 615, Visits: 447

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...



Post #475567
Posted Thursday, March 27, 2008 9:56 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,478, Visits: 1,402
Good article with decent examples. Cheers!!!!:)


Post #475869
Posted Wednesday, April 2, 2008 2:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 6,748, Visits: 8,546
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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #478291
Posted Wednesday, June 11, 2008 4:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 5, 2010 1:12 AM
Points: 14, Visits: 135
Nice article
Post #515040
Posted Wednesday, August 20, 2008 11:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 4, 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 »

Add to briefcase

Permissions Expand / Collapse