Technical Article

Monitor Object Changes

,

Based on post http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=4349 I decided to write this script which will check for object changes in all databases and send an email listing the objects changed to the Operator associated with the job. This by no means offsets the need to restrict change access to objects in production, but in those cases when you can't restrict it is a nice basic monitor.

The first part of the script will create the job (you need to change the @owner_login_name and @notify_email_operator_name information) and the second part creates the procedure to check for changes.

Pretty simple but effective.

/*Create the job to run the object change monitor procedure.
*/
declare 
@ReturnCode int,
@JobID binary(16)

select @ReturnCode = 0   

if exists (
select * from msdb..sysjobs where name = '#DBA - Monitor Object Changes')
exec msdb.dbo.sp_delete_job @job_name = '#DBA - Monitor Object Changes' 

-- Add the job
exec @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output , @job_name = N'#DBA - Monitor Object Changes', @owner_login_name = N'DOMAIN\Account', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 2, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0, @notify_email_operator_name = N'Public, John'
if (@@error <> 0 OR @ReturnCode <> 0) goto QuitWithRollback 

-- Add the job steps
exec @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Check for Object Changes', @command = N'exec vsp_MonObjChanges', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
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 

-- Add the job schedules
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'MonObjChanges_Sched1', @enabled = 1, @freq_type = 4, @active_start_date = 20020523, @active_start_time = 231500, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback 

-- Add the Target Servers
exec @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
if (@@error <> 0 OR @ReturnCode <> 0) goto QuitWithRollback 

goto   EndSave              
QuitWithRollback:
  if (@@trancount > 0) rollback transaction 
EndSave: 

/*Create the procedure vsp_MonObjChanges.
*/
use master
go

if exists
(select name from sysobjects where name = 'vsp_MonObjChanges')
drop procedure vsp_MonObjChanges
go

create procedure vsp_MonObjChanges as

/*Create table DBAObjMon if it does not currently exist. 
*/if not exists 
(select name from sysobjects where name = 'DBAObjMon')

create table DBAObjMon (
DBName varchar(32),
Name varchar(128),
XType varchar(2),
CreateDT datetime
)

/*Clear out old records from DBAObjMon table 
*/delete from DBAObjMon

/*Declare necessary variables and set appropriately
*/declare 
@OperEmail varchar(32),
@ServerName varchar(32),
@SubjectMsg varchar(128)

set @OperEmail = 
(select email_address 
from msdb..sysjobs j join msdb..sysoperators o on j.notify_email_operator_id = o.id
where j.name = '#DBA - Monitor Object Changes'
)
set @ServerName = (select @@ServerName)
set @SubjectMsg = ('The following objects have changed on '+@ServerName+'!')

/*Populate the DBAObjMon table using the sp_MSforeachdb procedure. 
*/exec sp_MSforeachdb @command1 = 'insert DBAObjMon select ''?'' as DBName , name, xtype, crdate from ?.dbo.sysobjects where crdate > getdate()-1 and name not like ''#qtemp%'''

/*Send mail message with attachment that includes changed objects list to the 
Operator associated with the job.
*/if exists
(select DBName, Name, XType, CreateDT from DBAObjMon)
begin
exec xp_sendmail
@recipients = @OperEmail,
@subject = @SubjectMsg,
@query = 
'select DBName, Name, XType, CreateDT from DBAObjMon',
@attach_results = 'TRUE', @width = 500
end

go

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating