June 30, 2009 at 12:12 pm
I have a number of support staff that need to be able to access and manage SQL Server jobs but not have system admin privs.
I've followed documents that outline setting up proxies but this hasn't helped.
I'm guessing I need all the jobs/steps to be owned by the same proxy but somehow grant all my support users the ability to manage the jobs.
I've also added the SQL operator rol to each use but they still can't manage the proxy jobs or anyone elses.
Does any one have any ideas?
Thanks
Marcus
June 30, 2009 at 12:32 pm
This is actually thé weak spot of SQLAgent. :crying:
What I do is create a SQL user account, make that account jobowner of the jobs non-sysadmins are wanting to manage.
Also add that account to msdb db-group SQLAgentOperatorRole.
Grant this account usage of the proxy account you created.
Provide the userid and password to the "job admin".
Primitive, but it works.
ps; A job cannot be owned by a dbgroup or windows group !
Johan
Learn to play, play to learn !
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[/url]
- 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
June 30, 2009 at 3:32 pm
Thanks ALZDBA.
All my jobs todate have been owned by the same service account that has local admin rights.
Ok, to comfirm, now I'm using a proxy and a different domain account i.e domain\SQLJobAdmin and not the service account will I need to set any other permissions? can I give t he proxy sysadmin rights explicity so it can access all my DB's OR will I have to grant the domain user access DB by DB?
July 1, 2009 at 4:52 am
Marcus Wilkinson (6/30/2009)
Thanks ALZDBA.All my jobs todate have been owned by the same service account that has local admin rights.
Ok, to comfirm, now I'm using a proxy and a different domain account i.e domain\SQLJobAdmin and not the service account will I need to set any other permissions? can I give t he proxy sysadmin rights explicity so it can access all my DB's OR will I have to grant the domain user access DB by DB?
Avoid using accounts that have (local) admin rights !
On my boxes, the sqlaccounts that are job-owner, have the needed auth on the db-objects they need and are granted the use for the proxy account.
Each proxy account has been granted strictly the folder access that is needed to do the job they are designed for.
Keeping it nice and tight gives you more preparation, but for sure pays off in the long run (thing migration, ...)
Document it all (with justification !).
Johan
Learn to play, play to learn !
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[/url]
- 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply