cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Level 7

Custom alert for monitoring object changes

Trying to create an email alert when an OBJECT:ALTERED is either changed, deleted or altered.  What is the best way of handling this.

0 Kudos
2 Replies
Highlighted
Product Manager
Product Manager

Re: Custom alert for monitoring object changes

3 suggestions to consider for SQL Server:

  1. A somewhat related, but indirect way might be to alert whenever the execution plan changes for a query. See "Custom Alert - Execution Plan Changed for SQL"
  2. You could create a DDL Trigger and insert information into a table that you could then query in the custom alert.
  3. An older article (may need to update queries) on how to detect DDL and/or DML changes has several suggestions. The best for DDL is toward the end either leveraging the tracefile or querying sys.objects and sys.schemas. Then wrap one of those queries into a custom alert.

From sql server 2008 - How to detect any changes to a database (DDL and DML) - Database Administrators St... :

You can detect any ddl changes by using trace file. below is script to get changes.

SELECT
  te
.name AS eventtype
  
,t.loginname
  
,t.spid
  
,t.starttime
  
,t.objectname
  
,t.databasename
  
,t.hostname
  
,t.ntusername
  
,t.ntdomainname
  
,t.clientprocessid
  
,t.applicationname 
FROM sys.fn_trace_gettable
(
  
CONVERT
  
(VARCHAR(150)
  
,(
  
SELECT TOP 1
  value
  
FROM sys.fn_trace_getinfo(NULL) 
  
WHERE property = 2
  
)),DEFAULT
) T
INNER JOIN sys.trace_events as te
  
ON t.eventclass = te.trace_event_id
WHERE eventclass=164

You can detect any modification on table and stored procedure using this script:

SELECT
  SO
.Name
  
,SS.name
  
,SO.type_desc
  
,SO.create_date
  
,SO.modify_date
FROM sys.objects AS SO
INNER JOIN sys.schemas AS SS
  
ON SS.schema_id = SO.schema_id
WHERE DATEDIFF(D,modify_date, GETDATE()) < 50
AND TYPE IN ('P','U')

0 Kudos
Highlighted
Product Manager
Product Manager

Re: Custom alert for monitoring object changes

Additional queries and event id definitions to further customize what object changes you are interested in can be found in Pinal Dave's blog in the link "SQL SERVER - Who Dropped Table or Database? - SQL Authority with Pinal Dave"

0 Kudos