Execute permission was denied on the object 'agent_datetime', database 'msdb', schema 'dbo'

Question

the user gets this error when running the following statement:

select j.name as 'JobName',
run_date,
run_time,
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
run_duration
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobhistory h
on j.job_id = h.job_id
where j.enabled = 1
order by JobName, RunDateTime desc

To resolve this issue created a DB_executor role on msdb database and gave permission to the user to execute. That did not resolve the issue.
Also did this go to security->schema->dbo.. Double click dbo... then click on permission tab->(blue font)view database permission and feel free to scroll for required fields like "execute"....help yourself with choosing....and grant,with grant .... not sure if this resolve the issue. Any advices.

Answer 1

Wouldn't it be better to create a view with this query, and grant the user SELECT permission on the view?

Answer 2

Hi @Avyayah ,

I think you should provide the execute permissions on ‘agent_datetime’ function.

Please login with an admin user that have rights to change execute permissions:

GRANT EXECUTE ON [dbo].[XXXXX] TO [Username]  

Grant permission to db_owner role:
Open SSMS>>Security>>Logins>>go to properties of user
Click to User Mapping>>check the database where you are going to give the db_owner role
Below this on Database role membership select db_owner role

Giving everybody execution permission, but this is an extreme solution

GRANT Execute on [dbo].your_object to [public]  

Grant sysadmin server role to the user:
Open SSMS>>Security>>Logins>>go to properties of user
On Server Roles section check sysadmin