MSSQL permissions

Owned by Anastasios Thomaidis
Last updated: Nov 23, 2023 by Matthew Hierholzer
1 min read

This article outlines which permissions are required for MSSQL and Checkmk.

LAST TESTED ON CHECKMK 2.0.0P1

Table of Contents

The current implementation of the check uses the "trusted authentication," where no user/password needs to be created in the MSSQL server instance by default. Using this method, you needed to grant the user as which the Check_MK windows agent service is running access to the MSSQL database. Otherwise, you can configure the credentials of a database user which has permission to read the needed information from the server instance. Below you will find the minimum set of permissions required for System authentication or "Database user credentials".

Step-by-step guide

  1. In the case of system authentication, the below rights are granted by default to the SYSTEM user. You can easily see the roles/permissions using tools like SSMS  or sqlcmd utility.
    Screenshot of Object Explorer in Windows detailing the Login properties for a connection.

    .

  2. If you create a read-only Database user, then you can copy the permission like above to this user as well.
    .

  3. In addition to the above, some extra permissions are needed to monitor MSSQL jobs.  If you come across this error message in the agent output, then please add the following permissions 

    <<<mssql_jobs:sep(09)>>>
MSSQLSERVER ERROR: The SELECT permission was denied on the object 'sysschedules', database 'msdb', schema 'dbo'. (SQLState: 42000/NativeError: 229).
    use msdb;
go;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobschedules TO checkmk;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO checkmk;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobservers TO checkmk;
GRANT EXECUTE ON OBJECT::msdb.dbo.agent_datetime TO checkmk;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobactivity TO checkmk;
go;

    .

  4. In addition to this, please also make sure that the user  VIEW DATABASE STATE and VIEW SERVER STATE.