Prerequisites for monitoring SQL Database
This section provides information about the prerequisites for monitoring SQL Server instances.
Permissions for monitoring SQL database
Monitoring mirroring operations necessitates sysadmin privileges.
A Foglight user needs to be created on every database within the instance, otherwise, it cannot be monitored. New databases created after the Foglight agent installation need to be added either by running the permission script again or manually using CREATE USER <Foglight User> under the new database.
Following are the permissions required to monitor SQL server:
| Instance Level | |
|---|---|
| VIEW ANY DEFINITION VIEW SERVER STATE ALTER TRACE |
Granted for: Tracing a Session Deadlocks monitoring PI Change-Tracking |
| Database Level | |
|---|---|
| Map Foglight Login to a database user If a domain group with the appropriate permissions is used, there is no need to create a new user. |
|
| db_ddladmin | Granted for: Running DBCC commands for indexes |
| CREATE USER
It is a permission for Amazon RDS for SQL Server. |
Grant Execute on these master database objects:
| xp_enumerrorlogs xp_readerrorlog |
Granted for Error log monitoring |
Grant Select on these msdb database objects:
| log_shipping_monitor_primary log_shipping_monitor_secondary log_shipping_primaries log_shipping_secondaries log_shipping_primary_secondaries syscategories |
Granted for Log Shipping monitoring |
| sysjobactivity sysjobs sysjobhistory |
Granted for Jobs and Replication monitoring |
| dbm_monitor_data dbm_monitor |
Granted for Mirroring monitoring |
| sysalerts agent_datetime |
Granted for Agent alerts and services |
For Amazon RDS for SQL Server, the msdb database requires the SQLAgentUserRole fixed database role.
For Amazon RDS for SQL Server, Master user should have access to all databases in the instance.
Running the Grant Permissions Script
The file used for manually granting permissions, DB_SQL_Server_Grant_Permission_Script.sql, can be obtained by clicking on the ‘View script’ link located under the Instances table. This option is accessible through either of the following methods:
When running the Monitor SQL Database wizard, the script link is in the Insufficient Privileges dialog screen. In the Cartridges - Components for Download screen.
Running this file requires one of the following server roles:
Server admin
Active Directory admin
Member of the db_owner
To manually run the Grant Permissions script:
- Run the CREATE USER command on a database to be monitored. Upon successful execution of this command, the login is transformed into a user within the designated database, thereby granting the ability to read the metadata of the database.
- Open the DBSS_SQL_Permissions_User_Databases.sql file in SQL Server Management Studio (SSMS).
- Find the Select @LoginName = ? section at the beginning of this file.
- Replace the question mark with the login name to which the requested permissions are to be assigned.
- Execute the script
After setting the pre-requisites, you can install and monitor a SQL server instance. For more information, refer to Installing and Monitoring a Single SQL Server Instance.
