question

SahaSaha-5270 avatar image
0 Votes"
SahaSaha-5270 asked Yufeishao-msft answered

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

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.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

Hi @SahaSaha-5270,

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




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.