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 ««12

Non-sysadmin needs ability to maintain SQL Agent Jobs Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 8:26 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 6, 2014 7:07 AM
Points: 412, Visits: 980
Thanks for all the suggestions and tips this has been very helpful if only to verify that we have already tried it or to give us some new direction.
Post #1467193
Posted Tuesday, June 25, 2013 9:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
What is the final verdict? How did you eventually meet the customer requirement? Custom security table.....security trigger.....etc?
Post #1467218
Posted Tuesday, June 25, 2013 9:21 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 6, 2014 7:07 AM
Points: 412, Visits: 980
We dont have a good solution but we do have a work around.

Basically right now we have a user that sets up jobs as themselves and once the job is setup correctly they change the owner to our standard ssis job owner acct. But, once that is done they are no longer able to make changes. So this person then uses the credentials of the ssis job owner acct to login and make changes.

Based on the posts I've seen Microsoft has not provided a clean way for non-sysadmins to manage jobs.
Post #1467229
Posted Friday, January 3, 2014 2:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 4:58 AM
Points: 1, Visits: 2
It can by done by overriding of sp_update_job procedure. Steps described here: http://borishristov.com/blog/modifying-not-owned-sql-agent-jobs-without-being-a-sysadmin/
Post #1527409
Posted Thursday, February 13, 2014 3:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:00 PM
Points: 25, Visits: 282
Tom Van Harpen (6/25/2013)
We dont have a good solution but we do have a work around.

Basically right now we have a user that sets up jobs as themselves and once the job is setup correctly they change the owner to our standard ssis job owner acct. But, once that is done they are no longer able to make changes. So this person then uses the credentials of the ssis job owner acct to login and make changes.
....


This is interesting. Perhaps create a stored procedure which will make this user the owner of this specific job. Grant execute to the new procedure for this user. Now the user can manage the job as needed without having access to the SSIS credentials. Additionally, you could schedule a job that will override the new stored procedure and revert the ownership to the SSIS account.



Blog: http://sqlexchange.wordpress.com
Post #1541427
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse