SQL Configuration Change Alerting

create database HCLDBA go ALTER DATABASE [HCLDBA] SET RECOVERY SIMPLE WITH NO_WAIT

GO

use HCLDBA goSET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[SQLConfig_Changes]( [TextData] [varchar](500) NULL, [HostName] [varchar](155) NULL, [ApplicationName] [varchar](255) NULL, [DatabaseName] [varchar](155) NULL, [LoginName] [varchar](155) NULL, [SPID] [int] NULL, [StartTime] [datetime] NULL, [EventSequence] [int] NULL ) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

SET NOCOUNT ON

GO

CREATE PROCEDURE dbo.usp_Capture_SQL_Config_Changes @SendEmailTo VARCHAR(255) AS CREATE TABLE #temp_cfg ( TEXTData VARCHAR(500), HostName VARCHAR(155), ApplicationName VARCHAR(255), DatabaseName VARCHAR(155), LoginName VARCHAR(155), SPID INT, StartTime DATETIME, EventSequence INT )

DECLARE @trc_path VARCHAR(500), @message VARCHAR(MAX), @message1 VARCHAR(MAX), @textdata VARCHAR(1000)

SELECT @trc_path=CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT) WHERE property=2

INSERT INTO #temp_cfg SELECT TEXTData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence FROM fn_trace_gettable(@trc_path,1) fn WHERE TEXTData LIKE ‘{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}Configur{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}’ AND SPID<>@@spid AND fn.EventSequence NOT IN (SELECT EventSequence FROM SQLConfig_Changes) AND TEXTData NOT LIKE ‘{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}Insert into #temp_cfg{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}’ ORDER BY StartTime DESC

INSERT INTO dbo.SQLConfig_Changes SELECT * FROM #temp_cfg

/*select TextData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence from fn_trace_gettable(@trc_path,1) fn where TextData like ‘{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}Configur{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}’ and SPID<>@@spid and fn.EventSequence not in (select EventSequence from SQLConfig_Changes) order by StartTime desc*/

–select * from SQLConfig_Changes

IF @@ROWCOUNT > 0 –select @@ROWCOUNT

BEGIN DECLARE c CURSOR FOR

SELECT LTRIM(REPLACE(SUBSTRING(TEXTData,31,250), ‘. Run the RECONFIGURE statement to install.’, ”)) FROM #temp_cfg

OPEN c

FETCH NEXT FROM c INTO @textdata

WHILE (@@FETCH_STATUS <> -1) BEGIN –FETCH c INTO @textdata

SELECT @message = @textdata + ‘on server ‘ + @@servername + CHAR(13)

EXEC msdb.dbo.sp_send_dbmail –@profile_name=’ProfileName – otherwise will use default profile’, @profile_name=’DBA’, @recipients=@SendEmailTo, @subject=’SQL Server Configuration Change Alert’, @body=@message

FETCH NEXT FROM c INTO @textdata

END CLOSE c DEALLOCATE c

END

DROP TABLE #temp_cfg

go

USE [msdb]

GO

/****** Object: Job [Detect Config Change] Script Date: 08/17/2011 12:22:12 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [Data Collector] Script Date: 08/17/2011 12:22:12 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Data Collector’ AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Data Collector’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Detect Config Change’, @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N’No description available.’, @category_name=N’Data Collector’, @owner_login_name=N’DOMAIN\USER’, @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Run the Stored Proc] Script Date: 08/17/2011 12:22:13 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Run the Stored Proc’, @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N’TSQL’, @command=N’USE [HCLDBA] GO

DECLARE @return_value int

EXEC @return_value = [dbo].[usp_Capture_SQL_Config_Changes] @SendEmailTo = N”emailid@domain.com”

SELECT

”Return Value” = @return_value

GO’, @database_name=N’HCLDBA’, @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Detect Config Change’, @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20110817, @active_end_date=99991231, @active_start_time=90000, @active_end_time=235959, @schedule_uid=N’deb2afc2-d84e-47fd-af68-661f550ad5a5′ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N’(local)’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:

GO