"The EXECUTE permission was denied on the object agent_datetime" in SQL Server logs (4225909)

"The EXECUTE permission was denied on the object agent_datetime" in SQL Server logs

Errors for the Execute permission on agent_datetime object similar to the following appear in the SQL Server log file for numerous collections such as

CAUSE 1
Missing permissions. The permissions script had not been run since the 5.5.8 version of the SQL Server cartridge in 2011. There have been significant changes in permissions requirements since this time specifically for the Agent Job List collection to determine the start and end date/times of jobs.


CAUSE 2
An instance may lose specific msdb database permissions after their weekly/monthly SQL Server Critical Update patching rounds. This forces the users to re-grant these permission for all post-patched SQL Server instances.

RESOLUTION 1

Workaround
Rerun the SQL Server permissions script using the validate connection functionality in the database agent or using the grant permissions script.


Status
The deployment guide only includes requiring the SELECT permission on agent_datetime and this additional requirement should be added. Enhancement ID FOG-8387 was logged to document the execute permission requirement on msdb.agent_datetime. This will be included in a future release of the Foglight for SQL Server documentation and Foglight for Databases deployment guide.


RESOLUTION 2

Workaround
Rerun the SQL Server permissions script using the validate connection functionality in the database agent or using the grant permissions script.

Status
Enhancement ID FGSS-I-103 has been logged to change the collections not to use msdb.agent_datetime for the Agent Job List collection when working with the start and end date for jobs. One possibility may be using two variables for the date and time parts, then concatenating and casing them into a valid date time. This will be reviewed by Product Management for consideration in a future release of the SQL Server cartridge.