The System Center Operations Manager 2012 is Microsoft’s flagship product to monitor various aspects of the infrastructure. Specific details about the product can be found in the TechNet at http://technet.microsoft.com/en-us/library/hh205987.aspx. Monitoring can be “extended” to SQL Server instances (2005 and later versions only) using the System Center Monitoring pack for SQL Server. Once the pack is imported into SCOM it can be configured to scan the network (discovery) for SQL Server instances or alternatively they can be be entered manually. The exact procdure how to do this can be found in the product documentation. In this post I wanted to explian another aspect.
Pages 29 to 33 of the SQL Server Management Pack Guide for System Center Operations Manager explain how to implement the principle of least privilige for the SCOM service configuration (and the Management Pack) when accessing SQL Server instances. The scenario is for an environment where servers are joined to a domain.
For specifics, from this point forward please refer to the document. The first step is to create domain users and groups which are then granted specific Group Membership and Policy Settings on each server that will be monitored. You might be able to get away with this administrative overhead by using GPOs – this should be discussed with your domain administrators.
The next step is to grant permissions within the SQL Server instances. Logins have to be created and granted VIEW ANY DEFINITION and VIEW SERVER STATE rights. The same logins also have to be created in system database and each user database (!!). In the case of attached or restored databases the DBA has to remember to grant the permissions. Finally the logins have to be added to a database role in the MSDB. For those who choose this route a sample script is included. In my opinion this is a very high administrative overhead, both when deploying and even more after. Moreover, post-deployment this procedure is prone to failure since there is a dependency on the DBA remebering to create a user in each attached or restored database. I cannot understand how Microsoft missed this.
Alternatively you can throw the SCOM SQL Server Management Pack Low-Privilige Access document out of the window, have the SCOM service (on each machine) running using the Local System account, and the Local System account created as an SQL Server login which would also be a member of the sysadmins fixed server role. During testing, the only problem I found with this setup is that if you want to implement a process such as the one explained in the Automatically Restore a Database to Test Backups post, database connections for sysadmin members are not killed and the Automated Restore will fail.
It’s really up to your company policies and whether an agreement is reached between the Operations and Security team/section/department. Whichever option you choose, my suggestion is that the number of sysadmin members is limited as much as possible.