May 26, 2011 at 8:05 am
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
May 26, 2011 at 8:14 am
Have you looked at the MSDB database fixed roles? Making the web account a member of one of those roles may assist you.
May 26, 2011 at 8:15 am
I think you can consider some new roles available in SQL Server like the ones below.
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
M&M
May 26, 2011 at 1:39 pm
That's exactly the advice I was looking for. This solved my problem and fed my brain. I appreciate the responses. 😀
May 27, 2011 at 5:04 am
You are welcome. Glad we could help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply