Job Running Status permission
-
Please help, I would like to give the developers the right to view jobs in EM and be able to check jobs status (i.e. running/not running). I've managed to set them up on MSDB and give them Database Role TargetServersRole with some permissions changed for the role. The developers are able to view, start, stop and modify jobs but they can not check running status. I've even gave them execute access on Master xp_sqlagent store procedures without luck. Does anyone knows what permissions they should have to allow them to view status, I do not fancy giving them System Administrator access right.
Thanks
Azz.
-
not sure what is needed in EM. Perhaps sysjobhistory has the status while it is running. You could try select access on that.
-
Why cant you check sp_who2 to see what is running??
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thanks for Steve and AJ, the problem is these guys need to check the jobs and run then in the Enterprise Manager, and as they had the SA permissions before and now we are tightening the security. Therefore I must find another way to give them these rights without the SA privileges.
-
Unless you want to re-code some MS supplied SPs, what you want to do CANNOT be done with SQL2000.
There are some critical SPs that check if you are either the job owner or a member of sysadmin, and if the answer is no you cannot process the job. You could recode these to adjust the checks, but if you do then you loose MS support.
The solution we use here is to have a standard job ownership account, and make all relevant jobs owned by this account. Anybody who needs to run one of these jobs signs on with the account and can run what they want. This is far from ideal, but we thought it is the least bad solution.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
I guess I am confused by what you are terming "running status" Does this mean the job complete/fail?? IF yes you can check sysjobhistory.....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
AJ,
In EM once you start a job, you can refresh the screen to see its status. So far only Sysadmin members can see the status (Executing/Not Running).
-
Ok, so from the GUI you want them to be able to see current job status. And having them check sp_who2 is not an option..
Sounds like you have unrealistic requirements... IF they used to have it and it was removed for a reason.... Why not give them the bare minimum sp_who2 (which would show job running) IF job isnt there it isnt running....
I understand the problems with removing permissions, etc.. however, the developers will need to get used to it as well and realize they can't have everything they used to by "back-door" patching....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
We are having the same issue here as we are tighting security and need to give them ability to run and view agent jobs, however I would have to see if I came across the same situiation with them not being able to see the status of the job.
I would like to say one of the considerations we are having redesigning the context of the process of the agent job. for example, there is a job our client wants to execute manually as needed basis but the he can now because we removed the SA acct. His job contains batch code which I am going to make into a batch file with multiple steps and have him have access to a directory with exec rights. Also the T-sql involved can be made coded by calling a store proc and they can call other store procs within that one and just give them exec rights to that proc.
Hope this opens the door with some options.
-
I developed a sproc to check all the jobs' status on a box. And, grant exec to developers.:P
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply