Monitoring errorlog/db jobs without 'sa' role | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Monitoring errorlog/db jobs without ‘sa’ role

Hi all, Need to know how to do the following from Enterprise Manager without ‘sa’ role, (when tried, encountered permission problems) 1. To monitor the errorlog from Enterprise Manager.
2. To monitor the job history of the jobs owned by ‘sa’ from Enterprise Manager. Thank You.
1. Its not possible until the login is a member of SYSADMIN and other way round is to read ERRORLOG from MSSQLLOG directory which is not supported as a DBA, but for the current scenariao it helps.
2. I think even its not possible until the login is a part of SYSADMIN.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I havent specifically tried it, but for #2, if you could get read permissions to the msdb database, you could write queries to get job history, regardless of who owns them. There are tools out there who can monitor both of the items you’re enquiring about, if thats all you’re looking for. The option Satya mentioned for 1 and what I said for 2, are really what any (worth its money) tool would do. You can do both in-house without tools, but as the approaches to both are not officially supported by MS, you may need to re-write the entire process when a new version of sql comes out/gets installed. Chris
Hi Satya/Chris, Thanks for your response. I tried giving ‘select’ permissions on various tables in msdb to my id (not part of SYSADMIN) and still it gives me problems. The purpose of this is to surrender our ‘sa’ id and as DBAs we are not supposed to own any ids that are part of SYSADMIN due to security reasons. I’m also concerned about the third party tools if they require SYSADMIN ids to monitor the errorlog and jobs. I know from the console, if we have read access to the server errorlogs, we can still view them from explorer. But we manage many of the systems remotely and we use Enterprise Manager as the only tool to connect all our servers. Not sure if this kind of a security requirement is implemented in other shops. If yes, can someone let me know how they successfully did the same ? Thanks a lot for your time.
Kanthi
Kanthi, have you tried giving your ID dbo on the MSDB database ? That worked now when I tested it, selecting from sysdbmaintplanhistory from msdb Panic, Chaos, Disorder … my work here is done –unknown
BOth the tasks are mentioned for DBAs and logins those are members of SYSADMIN, I think from SQL 2K SP2 you can add logins to TargetServerRole which is used for Target servers to be able to see jobs on a Master server. It was a potential security issue to allow members of that role to be able to execute/monitor jobs. If you’re not in SYSADMIN group and if you want to administer the SQL is impossible, for a DBA mapping login to SA is must and should. Refer to the Technet document I’d referred to you before (in one thread) and tighten up the security. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Okay here is one tweak which mau help you out…. Create a stored procedure in master / msdb fror 1, 2 respectively which does the required function. Now grant execute permission for the same to the login in discussion. I think executing the new created stored procedure should give u the results. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
]]>