August 20, 2003 at 5:54 am
Hi, hopefully there is an answer to this one. I have been given an objective to "Work with operations team and delegate monitoring if SQL backups/transaction log backups overnight where useful and appropriate"
OK, it is a simple job to create a new operator on each server "Ops_Team" and to set this up to receive e mails relating to backups jobs during a specified period (17:30 - 09:00).
The tricky bit appears to be to allow the Ops team to use Enterprise mananager to view and control SQL jobs relating to backups. I want to do this with the minimum possible SQL permissions and server access and certainly do not want to make the ops team SQL sysadmins.
Does anyone have any thoughts on how to acheive this, or indeed if it is possible in the first place?
Edited by - exonjg on 09/02/2003 08:06:33 AM
August 20, 2003 at 6:35 am
You might try adding opsTeam as a db_backupoperator for all existing databases (including model). Change backup jobs so they're owned by opsTeam. To manipulate and rerun backup jobs though you might have to grant various execute privileges within msdb.
Cheers,
- Mark
Cheers,
- Mark
August 21, 2003 at 6:26 am
If you use DB Maint Plans, the last tab allows you to log the results to another server. I set up our plans to log to a common server and run reports from that server. The next steps were to create a table of expected backups, create views with outer joins to find 'missing' jobs, running reports and sending e-mails of backup status.
Might be overkill depending on how many servers they need to check. It does allow for verification with read only access to one table (sysdbmaintplan_history) in msdb on the 'logged to server'. Each server doing the logging must have datawriter access to the 'logged to server'. We set up a domain group for that purpose.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply