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


Non-sysadmin needs ability to maintain SQL Agent Jobs


Non-sysadmin needs ability to maintain SQL Agent Jobs

Author
Message
Tom Van Harpen
Tom Van Harpen
SSC Eights!
SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)

Group: General Forum Members
Points: 908 Visits: 1102
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.
sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2502 Visits: 487
What is the final verdict? How did you eventually meet the customer requirement? Custom security table.....security trigger.....etc?
Tom Van Harpen
Tom Van Harpen
SSC Eights!
SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)SSC Eights! (908 reputation)

Group: General Forum Members
Points: 908 Visits: 1102
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.
priesol
priesol
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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/
Gary Gwyn
Gary Gwyn
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 572
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
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