August 22, 2011 at 12:47 pm
I have a 100 + Jobs running on our production SQL server and these jobs are running/owned under service account which is also used to run the SQL services it self.
In our environment we have different departments and few developers from each dept need to edit these jobs.
Questions :
1) Is their any way I can give access to devs just to edit the sql jobs without giving "sa" rights?
August 22, 2011 at 1:11 pm
Have your System/Domain admin create a security group in Active Directory and add in all the developers who should have this ability. Once that's created, add that group as a login on the SQL Server, choose which DB's they should have access to (i.e. msdb, etc).
Based upon how you set up your SQL server you should be able to grant the user/group SQLAgentUserRole, this will allow them to 1) See the SQL Server Agent, 2) Create/Edit jobs and 3) Run them as needed.
You could also create a role, and add in the AD group that way and grant access to the SQL Agent via the group.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 22, 2011 at 1:56 pm
thanks for the reply.
My problem did NOT solved....
The sql jobs cannot be owned by a Group/Role. It has to be a account or user.
As all my jobs are running under specific account, Even after giving SQLagentoperator rights and db_owner rights on msdb, the devs are not able to edit as jobs are owned by different account(service account).
1 way to fix this is making them owners of the job ,which I am not willing to do.
Any help is really appreciated...
August 22, 2011 at 2:19 pm
I think you may have misunderstood...(or maybe I did) but you asked:
I have a 100 + Jobs running on our production SQL server and these jobs are running/owned under service account which is also used to run the SQL services it self.
In our environment we have different departments and few developers from each dept need to edit these jobs.
Questions :
1) Is their any way I can give access to devs just to edit the sql jobs without giving "sa" rights?
Yes, you can give access to the devs to edit jobs without giving "sa" rights. They do not need to "own" them, just edit and start/stop them correct? If so, "SQLAgentUserRole" and "public" is all they should need.
1. Either grant "SQLAgentUserRole" to each developer individually (not a great option if you have a lot of developers across a lot of departments), or create a security group in AD (eg: DOMAIN\SQLDevelopers), then adding all developers to it
2. In the SQL Server Security, Add the windows security group "DOMAIN\SQLDevelopers", go to user mapping, select the DB, and choose the "SQLAgentUserRole", etc.
This should be all that's needed.
We have the same set up here, we have a domain account used to run all of our services and is the owner for just about everything (the acocunt never expires and cannot get locked out). Yet, we allow our reporting developers to access jobs on specific servers.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 22, 2011 at 2:31 pm
Yes, you can give access to the devs to edit jobs without giving "sa" rights. They do not need to "own" them, just edit and start/stop them correct? If so, "SQLAgentUserRole" and "public" is all they should need.
Yes. Not only start/Stop I want them to edit the steps.
I just tested with an test account and the steps and owner blocks are graded out.
August 1, 2012 at 10:30 am
Running into the exact same issue; hundreds of SQL instances, with many having user jobs. AD group created for users that need to maintain/run the jobs. But you cannot make an AD group an owner of a job. If a user in that group is the owner of the job, no others in the 24x7 support group can maintain the jobs.
Would love to find a solution to this problem. Too many sa's and cannot get around that until we provide the capability to maintain jobs.
August 1, 2012 at 10:37 am
Create a proxy account for agent execution
...make that the default owner for all jobs. This will still allow your permissions to work for the groups that need them, and allow the proxy account to run them
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply