User Privileges Required for Monitoring Microsoft SQL server

1. To monitor a Microsoft SQL server 7.0/2000, all tests should be configured with the credentials of an SQL user with Sysadmin role.

2. To monitor a Microsoft SQL server of any other version, all tests should be configured with the credentials of an SQL user with the following privileges:

   • connect sql
   • view any database
   • view any definition
   • view server state
   • PUBLIC role
   • select privilege on the MSDB.DBO.SYSJOBS table
   • select privilege on the MSDB.DBO.SYSJOBSCHEDULES table
   • select privilege on the MSDB.DBO.SYSJOBHISTORY table

   • For the SQL Database Space test to run, the user configured for that test should additionally possess one of the following privileges:

     • The db_datareader privilege on each of the databases to be monitored; (OR)
     • execute permissions for the sp_spaceused stored procedure on each of the databases to be monitored.

   • For the SQL Job Status and SQL Job Details tests to run, the SQL user should be configured with an additional privilege as mentioned below:

     • select privilege on the MSDB.DBO.SYSJOBACTIVITY table
     • select privilege on the MSDB.DBO.SYSSESSIONS table

   • For the SQL Database Space By File Groups test to run, the user configured for that test should additionally possess one of the following privileges:

     • The db_datareader privilege on each of the databases to be monitored; (OR)
     • execute permissions for the sp_spaceused stored procedure on each of the databases to be monitored.

   • For the SQL Log Shipping Status test, the user should additionally possess the following privileges:

     • select privilege on the log_shipping_secondary_databases table

     • select privilege on the log_shipping_primary_database table

   • For the SQL Network test, the user should additionally possess the right to run the query specified against the query parameter of this test.

   • For the SQL Error Log test, the user should additionally possess the following privilege:

     execute permissions for the xp_readerrorlog stored procedure on each of the databases to be monitored.