Return
-
Title
"The EXECUTE permission was denied on the object 'sp_trace_getdata" error in logs when SQL Server deadlock collection fails -
Description
The SQL Server deadlock collection fails with the following error printed in the SQL Server agent log files:
-
Cause
Insufficient permissions for the database agent login id
This script can be used in SQL Server to check if the necessary permissions for the Deadlock collection has been granted
SELECTOBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name FROMsys.database_permissions pWHEREOBJECT_NAME(major_id) = 'sp_trace_getdata' -
Resolution
Rerun the SQL Server permissions script as described in KB 125508 or run the following command for the login id used by the Foglight SQL Server database agent on the monitored host
grant execute on sp_trace_getdata to public
DISCLAIMER:The information in the script provided is known to work successfully; however, it has not been officially tested by Quest Quality Control. If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by Quest Support and Development.Quest Support and Development recommend always making a backup of the current Foglight installation and database prior to execution of any scripts that may modify it.