Custom alert for monitoring object changes

Question

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

cpriddy · Answer

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"

cpriddy · Answer

3 suggestions to consider for SQL Server: * 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" * You could create a DDL Trigger and insert information into a table that you could then query in the custom alert. * 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 Stack Exchange : 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')