User Privileges Required for Monitoring Microsoft SQL server

  1. To monitor an 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.