Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

asked on

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.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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

Avatar of Dale Fye

ASKER

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.
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.
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.
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.
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.
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

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
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.
"localize the SP"?

I don't have an SP, this is about a job, and I don't see any permissions for the job
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.
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Have not had an opportunity to finish this yet.
Dale, this still on your track?
Yes, Vitor, still in my queue.  It looks like I might actually get back to this soon after dealing with other client priorities.

Dale
Thanks, guys, finally got back to this and figured it out with your help.