December 26, 2011 at 2:55 pm
Environment: SQL Server 2008R2.
Problem: "EXECUTE msdb.dbo.sp_send_dbmail" runs via SSMS 2008R2 via Query but fails as stored procedure job.
The following query:
-- Execute usp_MyStoredProcedure stored procedure in Master database.
Declare
@profilename sysname,
@RecipientsProxy VARCHAR(MAX),
@SendEmailOnly BIT,
@Debug BIT
SET @ProfileName = 'My Profile Name'
SET @SendEmailOnly = 0
SET @RecipientsProxy = 'me@mydomain.com'
SET @Debug = 0
EXECUTE Master.[dbo].[usp_MyStoredProcedure ]
@ProfileName,
@RecipientsProxy,
@SendEmailOnly,
@Debug
runs just fine in SQL Server Management Studio when run as Query but when run as a SSQL Server Agenct Job it failed with the following error:
Error formatting query, probably invalid parameters (SQLSTATE 42000][Error 22050] DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000][Error 2528]
I did not include the entire stored procedure as it appears to run okay via SSMS query. I can include it if someone needs to see it. I did not write the stored procedure.
I have seen several postings relating to this type of issue but have not found any answers.
I know that the stored procedure fails with "EXECUTE msdb.dbo.sp_send_dbmail". When this part of the stored procedure is commented out the SQL Server Agent job run just fine.
Any help on resolving this issue will be greatly appreciated.
Thanks,
Howard
December 26, 2011 at 3:42 pm
Post the entire proc please.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 26, 2011 at 3:49 pm
I did not write this proc. I had found it on the internet and it appears to be available to anyone who wishes to use it.
USE Master
GO
IF OBJECT_ID('dbo.usp_MyStoredProcedure', 'P') IS NOT NULL DROP PROCEDURE dbo.usp_MyStoredProcedure
GO
CREATE PROCEDURE dbo.usp_MyStoredProcedure
@ProfileName SYSNAME = NULL, -- Valid DB Mail Profile
@RecipientsProxy VARCHAR(MAX) = NULL, -- semicolon-delimited list of e-mail addresses
@SendEmailOnly BIT = 0, -- So we can send email of existing records in
-- the [Master].[dbo].[DBCCCheckDBOutput] table
@Debug BIT = 0 -- Show or hide verbose output
AS
SET NOCOUNT ON;
DECLARE
@BatchDate DATETIME,
@DBName SYSNAME,
@MachineName SYSNAME,
@InstanceName SYSNAME,
@pk TINYINT,
@SQL VARCHAR(8000),
@SQLServerInstanceName SYSNAME,
@YES BIT,
-- Error message vars
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200),
-- DB Mail Variables
@AttachQueryResultAsFileProxy BIT,
@Body NVARCHAR(MAX),
@BodyProxy NVARCHAR(MAX),
@NO BIT,
@Query VARCHAR(MAX),
@QueryProxy VARCHAR(MAX),
@Recipients VARCHAR(MAX),
@Subject NVARCHAR(255),
@SubjectProxy NVARCHAR(255)
-- Ephemeral Work tables
DECLARE @t TABLE
(
PK TINYINT IDENTITY,
DBName SYSNAME
)
-- Defint Constants
SET @YES = 1
SET @NO = 0
-- Set DBMail variables
SET @AttachQueryResultAsFileProxy = @YES
-- Variables
SET @BatchDate = GETDATE()
SET @InstanceName = CAST(SERVERPROPERTY('InstanceName') AS SYSNAME)
SET @MachineName = CAST(SERVERPROPERTY('MachineName') AS SYSNAME)
IF @InstanceName IS NULL
SET @SQLServerInstanceName = @MachineName
ELSE
SET @SQLServerInstanceName = @MachineName + '\' + @InstanceName
SET @SubjectProxy = @SQLServerInstanceName + ': DBCC CheckDB Results'
SET @BodyProxy = 'Here are the summary DBCC CHECKDB output lines.'
SET @QueryProxy = 'SET NOCOUNT ON;SELECT CAST(DBName AS VARCHAR(25)) AS DBName, DTStamp, LEFT(MessageText, 200) MessageText FROM Membership.dbo.DBCCCheckDBOutput WHERE IsSummaryLine = 1 AND DTSTamp = ( SELECT MAX(DTStamp) FROM Master.dbo.DBCCCheckDBOutput ) ORDER BY DBName;'
-- 1) Parameter validation
-- Parameter existence check:
IF @ProfileName IS NULL
BEGIN
RAISERROR( 'Please provide a DB Mail Profile Name', 16, 1 )
RETURN
END
IF @RecipientsProxy IS NULL
BEGIN
RAISERROR( 'Please provide a receipent email address', 16, 1 )
RETURN
END
-- Profile name validity check
BEGIN TRY
EXEC msdb.dbo.sysmail_help_profile_sp @profile_name = @ProfileName
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
RETURN
END CATCH
IF @SendEmailOnly = @NO
BEGIN
-- 2) Create or maintain work table
IF OBJECT_ID(N'Master.dbo.DBCCCheckDBOutput', N'U') IS NOT NULL
TRUNCATE TABLE Master.dbo.DBCCCheckDBOutput
ELSE
BEGIN
CREATE TABLE dbo.DBCCCheckDBOutput
(
PK INT IDENTITY CONSTRAINT PK_DBCCCheckDBOutput PRIMARY KEY,
ServerName SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
DBName SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Error INT NULL,
[Level] INT NULL,
[State] INT NULL,
MessageText VARCHAR(7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
RepairLevel INT NULL,
[Status] INT NULL,
[DbID] INT NULL,
ID INT NULL,
IndID INT NULL,
PartitionID INT NULL,
AllocUnitID INT NULL,
[File] INT NULL,
Page INT NULL,
Slot INT NULL,
RefFile INT NULL,
RefPage INT NULL,
RefSlot INT NULL,
Allocation INT NULL,
DTStamp DATETIME NULL,
IsSummaryLine BIT CONSTRAINT DF_DBCCCheckDBOutput_IsSummaryLine DEFAULT (0)
)
END
-- 3) Create a loop to perform DBCC CHECKDB against all databases other than TempDB
INSERT @t( DBName )
SELECT name
FROM master.sys.databases
WHERE database_id <> 2
WHILE EXISTS ( SELECT TOP 1 1 FROM @t )
BEGIN
SELECT TOP 1 @pk = PK, @DBName = DBName FROM @t
SET @SQL = 'SET NOCOUNT ON;DBCC CHECKDB(' + @DBName + ') WITH TABLERESULTS, ALL_ERRORMSGS;'
IF @Debug = @YES SELECT @SQL;
INSERT INTO [Master].[dbo].[DBCCCheckDBOutput]
(
[Error]
,[Level]
,[State]
,[MessageText]
,[RepairLevel]
,[Status]
,[DbID]
,[ID]
,[IndID]
,[PartitionID]
,[AllocUnitID]
,[File]
,[Page]
,[Slot]
,[RefFile]
,[RefPage]
,[RefSlot]
,[Allocation]
)
EXEC (@SQL)
-- Update BatchDate, ServerName, and DBName
UPDATE Master.dbo.DBCCCheckDBOutput
SET
DTStamp = @BatchDate,
ServerName = @SQLServerInstanceName,
DBName = @DBName
WHERE ServerName IS NULL AND DBName IS NULL
-- Mark summary line for given database for email since we can't use local variables for the email session
UPDATE Master.dbo.DBCCCheckDBOutput
SET IsSummaryLine = @YES
WHERE PK = ( SELECT MAX(PK) FROM Master.dbo.DBCCCheckDBOutput WHERE DBName = @DBName )
-- Remove record from work table
DELETE @t WHERE PK = @pk;
END
END
-- 4) Send email having summary lines of DBCC CHECKDB
BEGIN TRY
IF @Debug = @YES SELECT @QueryProxy
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@Recipients = @RecipientsProxy,
@Subject = @SubjectProxy,
@Body = @BodyProxy,
@body_format = 'TEXT',
@query_result_header = @YES,
@Query = @QueryProxy,
@attach_query_result_as_file = @AttachQueryResultAsFileProxy,
@query_result_width = 1000
END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
END CATCH
December 26, 2011 at 8:44 pm
i didn't se the specific error , just the "it fails" part even though it's set up right elsewhere;
typically that's due to permissions.
whever calls the sp_send_dbmail needs to be a member of the DatabaseMailUserRole in the msdb database;
it works via s cript when you run it, because as a sysadmin, you of course bypass the required security.
But when bob from accounting or some scheduled job calls the proc as a normal user, their credentials fail when the cross database call to msdb tries to send the mail.
to fix the issue, you would probab ly want to add a windows group to the msdb database as a user, and then put that group in the DatabaseMailUserRole role. I'd avoid being lazy and adding the public role to the same role, but that's a possbility.
you could also use EXECUTE AS on the proc so it runs under the context of a user that has access in both databases as well.;
Lowell
December 27, 2011 at 2:12 pm
Lowell:
Thank you for your response.
I believe that you are correct in that it is a permission problem.
I tried adding the With Execute As 'sysadmin' and got the following error:
Msg 15151, Level 16, State 1, Procedure usp_GetDBCCCHECKDBOutput, Line 305
Cannot execute as the user 'sysadmin', because it does not exist or you do not have permission.
I then tried adding the With Execute As 'myuser' where myuser has sysadmin rights. The procedure created without a problem but the email was still not sent.
As you suggested I probably need to add a windows group to the msdb database as a user and then put that group in the DatabaseMailUserRole. My problem is that I have never done this before and therefore I do know how to go about this.
Prior to this, using SSMS, I went to the msdb database > Security > Roles > Database Roles > DatabaseMailUserRole and on the General section I checked db_owner. That did not help but now I cannot uncheck db_owner. The check box is grayed out. Any idea as to how I can uncheck this?
Any further help will be greatly appreciated.
Howard
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply