• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1489
  • Last Modified:

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?
0
Pacita_Tibay
Asked:
Pacita_Tibay
4 Solutions
 
rjbookCommented:
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

SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole

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;

GRANT VIEW SERVER STATE TO [Domain\Developers];

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)

db_dtsadmin
db_dtsltduser
db_dtsoperator

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

db_ssisadmin
db_ssisltduser
db_ssisoperator
0
 
Pacita_TibayAuthor Commented:
Hi,

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:
GRANT VIEW SERVER STATE TO [Domain\Developers];
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.

Thanks.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rjbookCommented:
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.
0
 
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.

Thanks.
0
 
rjbookCommented:
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.
0
 
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!
0
 
Ferruccio_GuicciardiCommented:
Very handy :) thanks :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now