May 2, 2012 at 8:50 am
Hi,
Is it possible to give access to user's to alter jobs without giving them admin rights ?
I know that users can alter the jobs of their own by giving appropriate rights but I am looking for a solution,which should allow the users to edit the jobs created by someone else.
Here is my situation :
We have 10 BI devlopers they work on sql agent jobs and all of them need to edit the jobs depending on the requirement.
I use a one service account as owner for all jobs and none of the developers were not able to alter because they are not admins. I really dont want to give them admin rights just because they need to alter jobs.
Any solution is really appreciated
May 2, 2012 at 7:55 pm
To edit other people's job you have to be part of the sysadmin Role. What you might do is create a new login and add it to the sysadmin Role, and ask your devs to use that. Maybe one shared, or one per dev, up to you. Then have a Server-side Trace running watching that login(s) to make sure they only use it for job management. I had this issue at one shop and the solution (per compliance rules) was to deploy a third-party scheduler with a more granular permission model and leave SQL Agent for DBA jobs only.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 5, 2012 at 1:12 pm
a possibility is to create a SQL authenticated login (it has to be SQL authenticated) and instruct the BI developers to use that account when working with their Agent jobs (which will be owned by this login).
The login will need to be granted SQLagentoperatorrole at the very least and other permissions depending on what the jobs do, but sysadmin wont be necessary.
---------------------------------------------------------------------
May 5, 2012 at 3:12 pm
Good call George. I cannot think of any reason why you couldn't use Windows login though. You may need to use a proxy too, depending on what the jobs need to accomplish.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 6, 2012 at 1:38 pm
In our shop everyone gets their own individual windows login to connect to the domain and that login ( and the groups it belongs to) is used to control their access to servers and SQL instances, and whatever tools\apps they use in their day to day work, so for us sharing a single windows ID would be out of the question security wise. I imagine most shops work in the same way.
Unfortunately windows groups cannot own jobs, so that leaves a SQL authenticated ID. As you say, there is a good chance permissions to use a proxy account would be required, depending on the function of the jobs.
---------------------------------------------------------------------
May 6, 2012 at 2:42 pm
george sibbald (5/6/2012)
In our shop everyone gets their own individual windows login to connect to the domain and that login ( and the groups it belongs to) is used to control their access to servers and SQL instances, and whatever tools\apps they use in their day to day work, so for us sharing a single windows ID would be out of the question security wise. I imagine most shops work in the same way.
I have seen different shops vary wildly when it comes to segmenting logins and environments, from no effort at all and everyone using sa for everything, to each person having a mix of Windows and SQL Logins to get their jobs done (3 permanent per person plus shared logins was the worst I had seen). In the shop I currently work in, and many others I have worked in, shared Windows Logins are issued to handle things similar (but not exactly the same in terms of Agent) to what is being discussed. Using "Run As" to launch SSMS using the shared Windows Login is all that is required in the OPs scenario for the BI Devs to gain access to SQL Agent and manage jobs as you described, without needing sysadmin membership.
Unfortunately windows groups cannot own jobs...
I can see it as an inconvenience in this case...but I am having a hard time figuring how that could be handled in terms of picking a security context. Someone has to run the job, so which member of the group should run the job? If a Windows Group could own jobs it would seem like a "cart before the horse" moment, unless that Group's Login were in the sysadmin Role in which case Agent could defer to the SQL Server Service account to run the job per the usual move when a sysadmin owns a job.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 6, 2012 at 5:03 pm
'Unfortunately' purely in the sense that for this problem, what would at first might seem the obvious and most secure option of setting up a windows group and placing the devs in that group is not available.
---------------------------------------------------------------------
May 8, 2012 at 8:42 am
Thanks Folks. I will go with creating a SQL and will ask everyone to use as owner of jobs.
May 8, 2012 at 8:49 am
OK. You will probably find there are teething problems hitting 'permission denied' type errors but you can just work through them.
---------------------------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply