February 10, 2010 at 3:07 am
I need to set up a user to be able to trigger a SQL Server Agent job from within a macro embedded in Excel.
Currently he's receiving an error that he doesn't have permission to execute the stored proc sp_start_job in the msdb database.
What server roles and database roles do I need to grant this user to enable him to trigger this proc?
Thanks,
Paul
Keep the rubber side down and the shiny side up.
February 10, 2010 at 9:53 am
I had a similar problem with allowing a job to be started from an asp page. You may be able to adapt this to your situation.
1. Create a SQL Server login and a user for it in msdb. Grant membership to the SQLAgentUserRole. This role allows the user to start jobs that it owns.
2. Create a user for the login in master with no permissions. This allows execution of xp_sqlagent_is_starting and xp_sqlagent_notify, which are executed from msdb.dbo.sp_start_job.
3. Create an unscheduled job, owned by the SQL Server login created above.
4. Create a stored procedure in a user database that executes sp_start_job as the login that owns the job. The procedure uses EXECUTE AS to switch security context.
CREATE PROCEDURE [dbo].[Execpkg]
@JobName varchar (20)
WITH EXECUTE AS 'jobownerlogin'
AS
exec msdb.dbo.sp_start_job @job_name = @JobName
REVERT
5. Grant EXECUTE permission on the stored procedure to the user's login.
Note: this solution requires that cross database ownership chaining be enabled, which may or may not work for you depending on your security standards.
Greg
February 11, 2010 at 4:28 am
Thank you Greg, I think that will work very well.
Paul
Keep the rubber side down and the shiny side up.
February 12, 2010 at 6:53 am
Could you share the Macro that calls the sql stored procedure you wrote in Excel? I think that could come in handy sometime...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply