Configuring the eG Agent with the Permissions Required for Running Queries

As mentioned earlier, the eG agent requires SELECT privileges to the ExecutionLog, Catalog, runningjobs, and users tables of the Microsoft SQL Report Server database. For this purpose, you can create a special user on the Microsoft SQL server, grant the required monitoring privileges to that user, and configure the eG agent with the credentials of that user.

To create such a user, do the following:

  1. Login to the Microsoft SQL Server Management Studio as a user with SYSADMIN privileges.
  2. Use the following script to create a special user with SELECT privileges to the ExecutionLog, Catalog, runningjobs, and users tables of the Microsoft SQL Report Server database:

    USE [master]

    GO

    CREATE LOGIN <Name_of_new_user> WITH PASSWORD=N'<Password_of_new_user>', DEFAULT_DATABASE=[<SQL_Report_Server_Database_Name>], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    use [<SQL_Report_Server_Database_Name>]

    GO

    CREATE USER <Name_of_new_user> FOR LOGIN <Name_of_new_user>

    GO

    USE [<SQL_Report_Server_Database_Name>]

    GO

    ALTER USER <Name_of_new_user> WITH DEFAULT_SCHEMA=[dbo]

    GO

    GO

    GRANT SELECT ON ExecutionLog TO <Name_of_new_user>

    GO

    GRANT SELECT ON Catalog TO <Name_of_new_user>

    GO

    GRANT SELECT ON runningjobs TO <Name_of_new_user>

    GO

    GRANT SELECT ON users TO <Name_of_new_user>

    GO

  3. Edit the script to replace the following variables with relevant values:

    <SQL_Report_Server_Database_Name> - Specify the correct name of the SQL Report Server database.

    <Name_of_new_user> - Specify the name of the special user.

    <Password_of_new_user> - Specify the password of the special user.

    For instance, to create a user named ‘reportdbuser’ with password ‘rdb’ for the SQL Report Server database ‘reportdb’, your script should be changed as shown below:

    USE [master]

    GO

    CREATE LOGIN reportdbuser WITH PASSWORD=N'rdb', DEFAULT_DATABASE=[reportdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    use [reportdb]

    GO

    CREATE USER reportdbuser FOR LOGIN reportdbuser

    GO

    USE [reportdb]

    GO

    ALTER USER reportdbuser WITH DEFAULT_SCHEMA=[dbo]

    GO

    GO

    GRANT SELECT ON ExecutionLog TO reportdbuser

    GO

    GRANT SELECT ON Catalog TO reportdbuser

    GO

    GRANT SELECT ON runningjobs TO reportdbuser

    GO

    GRANT SELECT ON users TO reportdbuser

    GO

  4. Finally, when configuring the tests for the managed Microsoft SQL Report Server component using the eG admin interface, make sure you configure the User and Password parameters of the tests with the username and password of the new user you created using steps 1-3 above.