Enable user to run SQL Server job

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.
LVL 52
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
That SP is in a system database called msdb so you'll need to give the execute permission for every single user (replace LoginName with the desired user login):
USE msdb
GO

GRANT EXECUTE ON sp_start_job TO LoginName

Open in new window

Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Vitor,

I get the following error message when I attempted that code:

Msg 15151, Level 16, State 1, Line 2
Cannot find the user 'loginName', because it does not exist or you do not have permission.

We are using Windows authentication and the username as shown in the Security\Logins section of the database is displayed as:

Domain\loginname

when I try to use that syntax in the GRANT line, SQL Server indicates "incorrect syntax near '\'

I currently have db_Owner permissions on msdb, but the user I want to grant permissions to only has public permissions to msdb.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I forgot to mention that you should first create the user in the database:
CREATE USER UserName FOR LOGIN [Domain\LoginName]

Open in new window

NOTE: UserName can be the same as LoginName if you don't want to give another name.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Dale FyeOwner, Developing Solutions LLCAuthor Commented:
this user already has a login on this server.

I'm still getting error:

Cannot find the user 'loginname', because it does not exist or you do not have permission.

Could this be a permissions issue for me?  If so, what permissions do I need in msdb?  I'm already configured as db_owner on msdb.
Vitor MontalvãoMSSQL Senior EngineerCommented:
this user already has a login on this server
But I'm refering to the msdb database. Does he have an user created in msdb?

Could this be a permissions issue for me?  If so, what permissions do I need in msdb?  I'm already configured as db_owner on msdb.
No, as db_owner you're allowed to create users in that db.
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Yes,

He is in the users list for the msdb database, but does not have any Owned Schemas or Role Members checked in that database.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, then the error isn't when you ran the CREATE USER command but the GRANT EXECUTE one, right?
If so then I might induced you on error because of my first post. Replacte the LoginName with the correct username:
USE msdb
GO

GRANT EXECUTE ON sp_start_job TO UserNameHere

Open in new window

Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Yes, that is correct, the error is encountered with the GRANT line.  I'm confused, you originally gave me:

GRANT EXECUTE ON sp_start_job TO LoginName

now you are saying use:

GRANT EXECUTE ON sp_start_job TO UserNameHere

His name show up as Domain\LoginName in the msdb=>Security>=Users section of the server as well as in the
Server=>Security=>Logins section of the SQL Server

but I'm still getting the error:

msg 15151, Level 16, State 1, Line 2
Cannot find the user 'username', because it does not exist or you do not have permissions
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry by the confusion. My first post was wrongly pointed to LoginName. Should always be the UserName. If they are the same then no issues. Can't see what might be wrong (maybe missing the [] ).
You can also perform this action with the GUI. Just localize the SP and use go to Properties, Security and give the execute permission to the desired user name. You can even press in the "Script" button to see what's the difference from mine.
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
"localize the SP"?

I don't have an SP, this is about a job, and I don't see any permissions for the job
Vitor MontalvãoMSSQL Senior EngineerCommented:
The original error message was:
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'
So, the users need to have permissions to run the stored procedure sp_start_job localized in database msdb, That SP is what allows them to start a SQL Server job and that's why they need permissions to execute it.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, this is a special stored procedure so looks like the GRANT EXECUTE may not work in this case.
I this MSDN article they say "Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users." so you'll need to run the following:
USE [msdb]
GO
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER UserName

Open in new window

Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Vitor,

I still have not been able to get this to work.  I've requested support from my clients sysAdmin to work on a couple of other things which I think might be playing a role on this, but he has not had time to support me lately.  Am still interested in getting this working, but currently have a work-around.

Dale
Jose TorresCertified Database AdministratorCommented:
There is a work-around.
But before I need to give my 2 cents.
I am not a big fan of using sp_start_job specially from outside of SQL server itself.
You need to understand what sp_start_job does. It starts a job and that is it, it wont wait for the job to finish if the job is started then the the script/code that call the sp_start_job continues.
Therefore it is very possible that you can have multiple instances of the job running.

That being said if you must
Create a stored procedure in a database (not in any system database)
CREATE PROCEDURE usp_start_job AS
EXECUTE AS USER = 'AdminUserNameHere'
exec msdb.dbo.sp_start_job 'Migrate_ProCount_to_P2_Staging'
REVERT

Open in new window

Then grant the user execute to this stored procedure

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Have not had an opportunity to finish this yet.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Dale, this still on your track?
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Yes, Vitor, still in my queue.  It looks like I might actually get back to this soon after dealing with other client priorities.

Dale
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Thanks, guys, finally got back to this and figured it out with your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.