Execute permission was denied on object ‘agent_datetime’

by

Issue

Some applications uses function in msdb database ‘msdb.dbo.agent_datetime‘ to convert run_date and run_time integer column values in date and time format.

Recently one of our client reported a issue where SSIS package suddenly started failing with error: “The execute permission was denied on the object ‘agent_datetime’, database ‘msdb’, schema ‘dbo’ .“

Cause

Upon checking with user, he confirmed that there were no changes made at application end and no permission changes were made at DB end either. Upon further check, we noticed that SQL server was recently patched with latest CU and post which SSIS package started failing with above error.

As part of script upgrade phase, objects inside the databases are upgraded based on recently patch applied. Below mentioned code is executed under script msdb110_upgrade.sql.

As existing function gets dropped and recreated, any permissions specific to this function gets removed.

PRINT 'Creating function agent_datetime...'
IF (NOT OBJECT_ID(N'dbo.agent_datetime', 'FN') IS NULL)
  DROP FUNCTION dbo.agent_datetime
go

CREATE FUNCTION agent_datetime(@date int, @time int)
RETURNS DATETIME
AS
BEGIN
 RETURN
  (
    CONVERT(DATETIME,
          CONVERT(NVARCHAR(4),@date / 10000) + N'-' +
          CONVERT(NVARCHAR(2),(@date % 10000)/100)  + N'-' +
          CONVERT(NVARCHAR(2),@date % 100) + N' ' +
          CONVERT(NVARCHAR(2),@time / 10000) + N':' +
          CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +
          CONVERT(NVARCHAR(2),@time % 100),
    120)
  )
END
go

Resolution

After finding the cause, it was easy to fix the issue. We just provided the execute permissions on ‘agent_datetime’ function to required login again and application process started working fine again.

Observation

With this case, we can say that we should also script out permissions given on system DB objects and match same permission level after any SQL update to avoid any surprises.

Hope this information helps!!
Happy learning!!

Post Views: 501