SQL Configuration Change Alerting

The below script helps in detecting, recording and alerting when ever a change is made to sp_config or SQL server configuration settings. This will help in pin-pointing any unnecessary changes happening to Our Beloved SQL Server…

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 ‘%Configur%’ AND SPID<>@@spid AND fn.EventSequence NOT IN (SELECT EventSequence FROM SQLConfig_Changes) AND TEXTData NOT LIKE ‘%Insert into #temp_cfg%’ 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 ‘%Configur%’ 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


Author: Sherbaz

Being a gadget enthusiast, He always felt good to try new programming languages and techniques. But never goes in-depth anywhere. www.SplitExpense.in was found when he tried php, html, javascript and mysql. Being an electronics engineer, he is also interested to build small gadgets and tools in embedded and digital electronics. As a profession, he handles microsoft sql server database and calls himself a database administrator

1 thought on “SQL Configuration Change Alerting”

Leave a Reply