August 14, 2014 at 9:19 am
Hello,
I'm not sure how to do this... so brainstorming time it is!
I have a group of users with db_owner privs. They are not sysadmin.
They wanted me to refresh their Prod db into Test at the drop of a hat.. which makes me grumpy...
so I created a fancy scripts that queries the PROD server for the last backup info (location of BAK files), and then it restores over the TEST environment. Easy peasy.
Then, I created a JOB to to execute the script on demand, and made one of the users the owner
then I created a Proxy account to let my user run the job.
The problem is that only the user who owns the job can run the job, nobody else..
What happen when that user calls in sick? (they call their DBA.. not a good idea... Don't Bother Asking)
So... how can I grant access for several users to be able to run the job?
(short of creating the same job for three people?)
August 15, 2014 at 10:44 pm
Go with the Force, Luke. A job really should have one owner, usually should be run on a schedule, and I believe they were designed overwhelmingly for the DBA to use, alone (they are not for mere mortals).
Have you tried running a job while it is already running, and have you seen the error that will be raised? Imagine what would happen if 50 people all tried running the same job at the same time - the DBA would get 49 calls... :w00t:. Of course, watching 50 people in a cat fight over who gets the next crack at running the silly job might be entertaining :cool:.
However, if you are going to let developers own the job, you ought to think long and hard about letting this cat out of the bag. Is a job even needed? If you have to follow PCI guidelines, access to production data may need to be constrained. Surely developers can schedule their own tasks, while you retain access control to production data. My company's developers are never a db_owner, and at best will be a db_datareader and db_datawriter for no more than 3 weeks (even in Development, assuming they are legitimately working on a manager-approved bug or feature request).
You can script out a job, change its name a little, and assign each job to a different login. But be prepared for the dark side.
August 16, 2014 at 2:54 am
You could create a stored procedure with EXECUTE AS using a user having the same login as it is assigned to the proxy being allowed to run the SQL agent job.
Within the sproc check if the job is running using
msdb.dbo.sysjobactivity where run_Requested_date is not null and stop_execution_date is null and Job_ID=@JobId
And start the job if it's not running already.
August 18, 2014 at 2:36 pm
Hello,
Will, BillHol gave me lots to think about.
In this particular case, since it's only a handful of people trying to run the job "once in a while", I guess we wouldn't into problem of them running at the same time... although when htey needed, they might.
That's why jobs are to be run by the DBA only... but that means bothering me. and you have no idea the amount of paperwork that involves.
Sadly, I work for a Increadible Big M.... which is a huge outsourcing company, so we ain't interested in best practices when "good enough will suffice"... and doing things faster cheaper is more important.
The best approach will be implement what LutzM suggests in order to avoid the "job running many times".
thanks for your help.
If I'm not part of "inhouse development", does that mean I'm part of "outhouse development"? 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply