Wouldn't it be better to create a view with this query, and grant the user SELECT permission on the view?
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.
2 answers
Sort by: Most helpful
-
-
YufeiShao-msft 7,061 Reputation points
2021-09-23T02:31:42.9+00:00 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 roleGiving 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