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
@ProfileNameSYSNAME = NULL,-- Valid DB Mail Profile
@RecipientsProxyVARCHAR(MAX) = NULL,-- semicolon-delimited list of e-mail addresses
@SendEmailOnlyBIT= 0,-- So we can send email of existing records in
-- the [Master].[dbo].[DBCCCheckDBOutput] table
@DebugBIT= 0-- Show or hide verbose output
AS
SET NOCOUNT ON;
DECLARE
@BatchDateDATETIME,
@DBNameSYSNAME,
@MachineNameSYSNAME,
@InstanceNameSYSNAME,
@pkTINYINT,
@sqlVARCHAR(8000),
@SQLServerInstanceNameSYSNAME,
@yesBIT,
-- Error message vars
@ErrorMessageNVARCHAR(4000),
@ErrorNumberINT,
@ErrorSeverityINT,
@ErrorStateINT,
@ErrorLineINT,
@ErrorProcedureNVARCHAR(200),
-- DB Mail Variables
@AttachQueryResultAsFileProxyBIT,
@BodyNVARCHAR(MAX),
@BodyProxyNVARCHAR(MAX),
@NOBIT,
@QueryVARCHAR(MAX),
@QueryProxyVARCHAR(MAX),
@RecipientsVARCHAR(MAX),
@SubjectNVARCHAR(255),
@SubjectProxyNVARCHAR(255)
-- Ephemeral Work tables
DECLARE @t TABLE
(
PKTINYINT IDENTITY,
DBNameSYSNAME
)
-- 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,
ServerNameSYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
DBNameSYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
ErrorINT NULL,
[Level]INT NULL,
[State]INT NULL,
MessageTextVARCHAR(7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
RepairLevelINT NULL,
[Status]INT NULL,
[DbID]INT NULL,
IDINT NULL,
IndIDINTNULL,
PartitionIDINT NULL,
AllocUnitIDINT NULL,
[File]INT NULL,
PageINT NULL,
SlotINT NULL,
RefFileINT NULL,
RefPageINT NULL,
RefSlotINT NULL,
AllocationINT NULL,
DTStampDATETIME NULL,
IsSummaryLineBIT 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;'
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 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply