Return
-
Title
Execute permission errors in SQL Server agent collections -
Description
Numerous permissions errors in SQL Server 2012 agent
Several metrics were are limited by the execute permission message:
ERROR [MYAGENT-lowPriorityPool-7-[DBSS_SQL_Server_Connections_Summary][YYYY-MM-DD HH:MM:SS.SSS]] com.quest.qsi.fason.core.collection.processor.InstanceProcessor - Failed to run DBSSVersionInfoProcessor processor of DBSS_SQL_Server_Connections_Summary collection.
java.lang.RuntimeException: Failed to execute collection [DBSS_SQL_Server_Connections_Summary], reason=The EXECUTE permission was denied on the object 'xp_msver', database 'mssqlsystemresource', schema 'sys'ERROR [MYAGENT-lowPriorityPool-2-[DBSS_Logical_Disks][YYYY-MM-DD HH:MM:SS.SSS]] com.quest.qsi.fason.framework.connections.jdbc.JDBCConnectionImpl - Failed to close statement.java.sql.SQLSyntaxErrorException: [Foglight][SQLServer JDBC Driver][SQLServer]The EXECUTE permission was denied on the object 'sp_executesql', database 'mssqlsystemresource', schema 'sys'. -
Cause
EXECUTE permission was denied on the object 'sp_executesql', and 'sp_trace_setevent', in the database 'mssqlsystemresource'
By default, all SQL server users are granted Execute permission on the sp_executesql stored procedure. If this permission is revoked during SQL Server hardening procedures, Foglight SQL Server agent collections may fail to run.
-
Resolution
Manually add execute permissions to 'xp_msver' and 'sp_executesql'
To resolve this with the sp_executesql permissions, perform these steps:- Grant the execute permission to the Foglight database agent user on the stored procedures.
To grant the sp_executesql execute permission to the Foglight database agent user:
- Connect to SQL Server Management Studio as the SQL Administrator (sa).
- Under the Databases folder, expand master and Programmability.
- Expand Extended Stored Procedures and System Extended Stored Procedures.
- Right-click sp_executesql and click Properties
- Select Permissions.
- Click Search to open the Select users or users dialog box.
- Click Browse.
- Select the checkbox next to the Foglight database agent user.
- Click OK.
To grant the xp_msver execute permission to the Foglight database agent user, run the following command in the monitored database:
USE msdb; GOGRANTEXECUTEONsp.xp_msverTO[Domain\Account] GO