Allowing SCOM to Monitor SQL with Local System
Configuring SQL Monitoring with Local System
In previous versions of SQL, Local System had access to SQL (unless removed by administrators, a known best practice). However, newer versions do NOT grant this access out-of-the-box. To allow the SCOM agent running under Local System to monitor SQL (engine, instances, and DBs) you need to make the following changes.
- In SQL Server Management Studio, create a login for “NT AUTHORITY\System” on all SQL Server instances to be monitored on the agent machine, and grant the following permissions (Securables page of the Login Properties page) to the “NT AUTHORITY\System” login:
- VIEW ANY DATABASE
- VIEW ANY DEFINITION
- VIEW SERVER STATE
- Create a NT AUTHORITY\System user that maps to the NT AUTHORITY\System login in each existing user database, master, msdb, and model. By putting user in the model database, it will automatically create a NT AUTHORITY\System user in each future user-created database. You will need to manually provision the user for attached and restored databases. (Note: you can do this via the User Mappings page on the instance login created in step 1. Simply check the DBs.)
- Add the NT AUTHORITY\System user on msdb to the SQLAgentReaderRole database role.
- Add the NT AUTHORITY\System user on msdb to the PolicyAdministratorRole database role.
Note: This won't necessarily allow you to run SQL tasks. See the MP Guide for details on the permissions required for that.