February 6, 2005 at 4:32 am
Hi, Iam having a DB and around 125 jobs performing operations on the DB.
I want ro create a login/user who should have access privileges only on the jobs and no other DB objects.This login must be able perform all operations like start,stop,view job history etc. on the jobs.
Wht DB/Server roles should i set in order to achieve the above.
Thanks in advance !!!!.
Regards
Praveen
February 7, 2005 at 3:11 am
The security model used in SQL2000 to manage SQL Agent jobs is very limited.
A user who is not a Syadmin can only manage jobs they own.
A user who is a Sysadmin can manage all jobs, plus (of course) do anything else within SQL.
You cannot have a situation where a user who is not a Sysadmin can manage jobs from multiple users. Ther are security various hacks published that extend the standard functionality a bit, but unless you want to alter system stored procedures to remove some hard-coded security checking you will not get to the situation you want.
Your only real choices are a) Use a third-party job management system (e.g. BMC Patrol, Tivoli, CA Unicenter, etc), which means you have no SQL Agent jobs; or b) Wait for SQL2005, which has better (but still not good) job management.
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
February 15, 2005 at 12:11 pm
Within the msdb database, there is a TargetServersRole role. I believe that adding your users to this role will provide the functioanality that you need.
By modifying the permissions granted to this role, I have used this to allow selected users to look at 'job histories', without being able to start or stop jobs.
February 15, 2005 at 12:32 pm
Praveen,
To add to jarney's reply, the specific permissions you'll need to grant TargetServersRole are EXECUTE on sp_start_job and sp_stop_job. The role had those permissions before sp3 was released.
Greg
Greg
February 21, 2005 at 8:50 am
Thank u all for your timely assistance.
Regards
Praveen
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply