Permission to run certain jobs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Permission to run certain jobs

We have a group of jobs that are scheduled on SQL Server and managed by a team of developers. In SQL 2000 we created an account the development team shared and that owned their jobs. This allowed them to submit, troubleshoot, view job output etc. This was not the best solution from an auditing standpoint as the shared account made it impossible to track who was logging in to complete this type of maintenance. The new MSDB database roles in SQL 2005 solve part of this problem as we can use it to grant the individual developers the SQLAgentReader role to allow them to view job output and the job steps using their individual accounts instead of a shared account. However, we’d really like to give them the ability to edit/start/stop THEIR jobs using their individual accounts. It’s my understanding that the SQLAGENTOperator role would allow them to control all jobs (including backups, reindex, etc that are maintained by the DBA staff) as well as their jobs. Is there a way to further "fine tune" the security on who has the ability to execute/edit jobs…. Thanks for your help!!

http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=137 see this FAQ. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Like I said in my first post, I’m already using the SQLAgentReaderRole to allow the developers to see their jobs and view the job output, but they wish to execute the jobs and I can’t find a way to give them execute privileges on their jobs without granting them execute privileges on ALL jobs.
That is still a limitation here and not possible to grant for a specific job. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Grant the user to "SQLAgentUserRole" in MSDB… then I believe user can create and start and stop his own jobs and jobs created by others who has same level permissions… but can’t see jobs owned by other user who has higher previlages than him… I have not tested completely…so please test before granting the access…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

.. the problem there is no such facility upto that granular level to own by a particular login. Say if login x & y has same db_datawriter privileges then both of them will be able to view their jobs. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>