Monitoring a job that our App kicks off...

  • Hi,

    We have a web application that serves as a control panel which allows an end user to click a button to kick off an SSIS package. We want to give the user visual feedback on the completion of the package. The direction we took was to call the system stored procedure MSDB.sp_help_job periodically to get the job's status. Unfortunately, we cannot assign permissions to system stored procedures and give the web account rights to run this sproc.

    Next we thought we would try the following (just an example of using EXECUTE AS)

    USE [DBName]

    GO

    /****** Object: StoredProcedure [dbo].[MSDB_sp_help_job] Script Date: 05/26/2011 09:36:37 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[MSDB_sp_help_job]

    WITH EXECUTE AS 'WebUserAccount' AS

    SELECT user_name();

    This returns the user_name as I would have expected 'WebUserAccount' . However, in my real test I will need to do more than "SELECT user_name();) so I changed this to use SA. When I do that I can't even save the SPROC. I get:

    Msg 15151, Level 16, State 1, Procedure MSDB_sp_help_job, Line 4

    Cannot execute as the user 'SA', because it does not exist or you do not have permission.

    I simply want to give read-only ability to learn job status w/o giving a user log in extended rights.

    I would like to find a way to use EXECUTE AS but am open to other ideas for checking job status.

    Thanks

  • Have you looked at the MSDB database fixed roles? Making the web account a member of one of those roles may assist you.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • I think you can consider some new roles available in SQL Server like the ones below.

    SQLAgentUserRole

    SQLAgentReaderRole

    SQLAgentOperatorRole

    M&M

  • That's exactly the advice I was looking for. This solved my problem and fed my brain. I appreciate the responses. 😀

  • You are welcome. Glad we could help.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply