How do I grant Developers access to the SQL Server Mgmt Jobs and Logs

Is it possible to grant developers (as db_owner) access  to the SQL Server Management Job and Error Logs without granting them as sysadmin?
Who is Participating?
rjbookConnect With a Mentor Commented:
The granting of permissions to SQL Agent jobs is fairly easy in SQL2005 and beyond depending how much permissions you want to give them. The are 3 Roles in the MSDB database that you can add the Developers to, just check out Books Online for these 3 topics


Each Role has different level of permissions. As far as viewing the SQL Error Logs I think you need to grant a Server Level Permission like so;


Also if you want to allow then to run Profiler Traces you need to grant this;

GRANT ALTER TRACE TO [Domain\Developers];

One more thing...if you need to allow them to execute SSIS packages in SQL 2005 there are 3 database Roles in MSDB (again just check BOL)


Interestingly (and appropriately) the roles are renamed in SQL 2008 to;

Pacita_TibayAuthor Commented:

Tried the above suggestions but not a complete solution to my question.  

In SQL Server 2000, I created a new role granting the necessary stored procedures (sp_add_job, sp_help_jobhistory, etc. etc as suggested by the 1st expert) in the  in msdb and gave the user this new role.  The user was able to access the Management-> Jobs and create new jobs, etc.  However, the user could not see the existing jobs.  These existing jobs were setup to run by the 'sa' account.  Also, user could not access the SQL Server logs.

In SQL Server 2005, the solution provided:
seem not working also.  User has been granted SQLAgentReaderRole and SQLAgentUserRole.

I'd like to grant the user access to the Management SQL Server Logs and SQL Server Agent Error Logs.

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

rjbookConnect With a Mentor Commented:
Sorry, I did not notice this was tagged for SQL2000. The only way I know of to grant a non Sys Admin the ability to run a job in SQL 2000 is to do the following; First add the User (or Group) to the TargetServersRole in the msdb. Next you need to over-ride the default permissions for that Role, right Click the Role in Enterprise Manager and go Properties, then click the Permissions button. Scroll way down until you see the sp_start_job permission which will be denied by default. Just click in checkbox to allow Execute permissions. Do the same on sp_stop_job if desired.

For the other issue (viewing the error log) that I do not know offhand. I do know that if you grant the View Server State (in SQL2005) they will be able to see the Activity Monitor Node in Management Studio which can be valuable to Developers.
Pacita_TibayAuthor Commented:
The first question was partiallly resolved by creating a new role with permissions to a number of stored procedures in msdb.  Then adding the user to this role.  The user can add new jobs.  However, user could not view the existing jobs, which are being run by the 'sa' account.

I'm still trying to find a solution about granting user to see the SQL Server log errors.

rjbookConnect With a Mentor Commented:
Well there is a rather "low tech" way for them to view the SQL Logs on a server assuming they have the ability to connect to the local file system. Have the Network Admin(which may be yourself) grant them read access to the following folder C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG They could then open the SQL Errror Logs or the SQLAgent Logs in a tool like Notepad (or your favorite text editor). Again just a workaround.
Pacita_TibayAuthor Commented:
Yes, that's correct and will use only if there is no other way from SQL Server Ent. Mgr.  Currently, user don't have access to the server but can be granted if needed. Thanks!
Very handy :) thanks :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.