?
Solved

How to run sp_start_job with in a stored procedure when the user does not have access to the MSDB database.

Posted on 2009-04-27
2
Medium Priority
?
305 Views
Last Modified: 2013-11-10
I have created a stored procedure that I would like to run from a SSRS report being used on a 3rd party software.  However the login to the 3rd party software does not have access to the MSDB database and I do not want them to have access to that database.  This report will give the end user the way to schedule the update without contacting me.  This is what I have for the sp:

create procedure lrp_Merge_Duplicates
as

execute msdb.dbo.sp_start_job 'Merge Duplicates'

Is there a I can do this.

Thanks,

Gabicus
0
Comment
Question by:Richard Comito
2 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 24246600
try this (see http://www.mssqltips.com/tip.asp?tip=1227 for details):
CREATE PROCEDURE lrp_Merge_Duplicates
WITH EXECUTE AS OWNER
as
execute msdb.dbo.sp_start_job 'Merge Duplicates'
0
 

Author Comment

by:Richard Comito
ID: 24246619
CGLuttrell,

Thank you!  That was it.

Gabicus
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question