Allowing users to run jobs via a SQLCMD batch file

[RRinTetons]

SQL Server 2008 R2

I need to provide a desktop utility to allow a couple of users to run a specific SQL Agent job on demand.

I created an sp in MyDB that calls sp_runjob for the job I want to run.

USE [MyDB]
GO
CREATE PROCEDURE MyJobRunner
AS
EXEC [msdb].[dbo].[sp_start_job] N'MyJob'
GO

I created a .cmd using

SQLCMD -S MyServer -d MyDB -Q "EXEC dbo.MyJobRunner"


I added the users to MyDB with Public. I granted execute on the sp to the users required. I added the users to the SQLAgentUserRole in MSDB.

I can run the utility, they can't.

I tried WITH EXECUTE AS OWNER and it didn't work for me, nor for them.

Any ideas what's up? What log an error would appear in? Better ways to do this?

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[gmmastros]

Did you add any security credentials to the cmd file. I see server, database, and the command to run, but nothing about logging in. If you want your users to log in with windows authentication, you should add -E to the cmd file. These things are case sensitive, so make sure you use upper case E. If you want them to log in with SQL Authentication, then you'll need to specify the user name and password within the cmd file.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RRinTetons]

According to the MSDN article at

http://msdn.microsoft.com/en-us/library/ms180944.aspx

The -E option is the default and does not have to be specified.

That said, I'll add it and see what I get.

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[RRinTetons]

Nope, no change.

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[gmmastros]

I hadn't realized it was optional. Thanks for that bit of information.

Have you tried adding the login as a user to the msdb database? I see that you added them to the SQLAgentUserRole, but try adding them as a user too.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RRinTetons]

Added the users to MSDB as db_owner (!), even that didn't work....

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort