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:
- Login to the Microsoft SQL Server Management Studio as a user with SYSADMIN privileges.
-
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
-
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
- 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.