I have an Access database which is linked to a SQL Server (2008 R2).
In the database, I have a procedure which allows me (dbo and sysadmin permissions) to run a job which pulls data from a linked server into our reporting server. When I run this procedure it executes a pass-thru query to the Server with syntax:
exec msdb.dbo.sp_start_job 'Migrate_ProCount_to_P2_Staging'
But when one of my users attempts to run this procedure, he gets the error message:
229 [Microsoft][SQL Server Native Client 10.0][SQL Server]
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'. ODBC.QueryDef
I am not a SQL Server expert, but have been successful so far due to the help of the SQL experts here at EE. What measures can I take to either modify the execute statement shown above or to assign permissions to this job to specific users.
Thanks for your help.