INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Allowing users to run jobs via a SQLCMD batch file

Allowing users to run jobs via a SQLCMD batch file

(OP)
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

RE: Allowing users to run jobs via a SQLCMD batch file

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

RE: Allowing users to run jobs via a SQLCMD batch file

(OP)
According to the MSDN article at

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

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

RE: Allowing users to run jobs via a SQLCMD batch file

(OP)
Nope, no change.

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

RE: Allowing users to run jobs via a SQLCMD batch file

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

RE: Allowing users to run jobs via a SQLCMD batch file

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close