0

I want to know if some data has just been deleted from my database and receive an e-mail alert with the table names which have been affected by the deletion .

I have set up SQL SERVER to send e-mails, but i do not know how to get the data as explained above and the immediate sending of the e-mail as well .

1
  1. You can use SQL Server Change Data Capture or Change Tracking features to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format.

To get the changed records for a table and information about these changes, use the CHANGETABLE(CHANGES…) function.

Drawback: is that you have to query the tables periodically and then send the notification email

  1. Create triggers on a table that needs auditing. Triggers will be fired when a record is inserted, deleted or updated and msdb.dbo.sp_send_dbmail should be used to send an email alert to a specified email recipient, immediately:

e.g.

CREATE TRIGGER t_Pers ON Person.Person 
AFTER INSERT, UPDATE, DELETE 
AS EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'Profie name', 
@recipients = 'abc.xy.com' , 
@body = 'Data in AdventureWorks2012 is changed', 
@subject = 'Your records have been changed' 
GO

As triggers are fired per batch, not per row, it should be ensured that all batch transactions are captured, and no data is lost.

Therefore as mentioned above both the above solutions are very much helpful, but depends upon how you want to use them and get benefited.

In addition, please read Tracking Data Changes from MSDN and Performance Tuning SQL Server Change Tracking

  1. The method which i have been using for such scenarios is analyzing the changes from default trace file which tracks object level changes using the below query as explained in this great article:The default trace in SQL Server - the power of performance and security auditing

        SELECT  TE.name ,
        v.subclass_name ,
        DB_NAME(t.DatabaseId) AS DBName ,
        T.NTDomainName ,
        t.NTUserName ,
        t.HostName ,
        t.ApplicationName ,
        t.LoginName ,
        t.Duration ,
        t.StartTime ,
        t.ObjectName ,
        CASE t.ObjectType
          WHEN 8259 THEN 'Check Constraint'
          WHEN 8260 THEN 'Default (constraint or standalone)'
          WHEN 8262 THEN 'Foreign-key Constraint'
          WHEN 8272 THEN 'Stored Procedure'
          WHEN 8274 THEN 'Rule'
          WHEN 8275 THEN 'System Table'
          WHEN 8276 THEN 'Trigger on Server'
          WHEN 8277 THEN '(User-defined) Table'
          WHEN 8278 THEN 'View'
          WHEN 8280 THEN 'Extended Stored Procedure'
          WHEN 16724 THEN 'CLR Trigger'
          WHEN 16964 THEN 'Database'
          WHEN 16975 THEN 'Object'
          WHEN 17222 THEN 'FullText Catalog'
          WHEN 17232 THEN 'CLR Stored Procedure'
          WHEN 17235 THEN 'Schema'
          WHEN 17475 THEN 'Credential'
          WHEN 17491 THEN 'DDL Event'
          WHEN 17741 THEN 'Management Event'
          WHEN 17747 THEN 'Security Event'
          WHEN 17749 THEN 'User Event'
          WHEN 17985 THEN 'CLR Aggregate Function'
          WHEN 17993 THEN 'Inline Table-valued SQL Function'
          WHEN 18000 THEN 'Partition Function'
          WHEN 18002 THEN 'Replication Filter Procedure'
          WHEN 18004 THEN 'Table-valued SQL Function'
          WHEN 18259 THEN 'Server Role'
          WHEN 18263 THEN 'Microsoft Windows Group'
          WHEN 19265 THEN 'Asymmetric Key'
          WHEN 19277 THEN 'Master Key'
          WHEN 19280 THEN 'Primary Key'
          WHEN 19283 THEN 'ObfusKey'
          WHEN 19521 THEN 'Asymmetric Key Login'
          WHEN 19523 THEN 'Certificate Login'
          WHEN 19538 THEN 'Role'
          WHEN 19539 THEN 'SQL Login'
          WHEN 19543 THEN 'Windows Login'
          WHEN 20034 THEN 'Remote Service Binding'
          WHEN 20036 THEN 'Event Notification on Database'
          WHEN 20037 THEN 'Event Notification'
          WHEN 20038 THEN 'Scalar SQL Function'
          WHEN 20047 THEN 'Event Notification on Object'
          WHEN 20051 THEN 'Synonym'
          WHEN 20549 THEN 'End Point'
          WHEN 20801 THEN 'Adhoc Queries which may be cached'
          WHEN 20816 THEN 'Prepared Queries which may be cached'
          WHEN 20819 THEN 'Service Broker Service Queue'
          WHEN 20821 THEN 'Unique Constraint'
          WHEN 21057 THEN 'Application Role'
          WHEN 21059 THEN 'Certificate'
          WHEN 21075 THEN 'Server'
          WHEN 21076 THEN 'Transact-SQL Trigger'
          WHEN 21313 THEN 'Assembly'
          WHEN 21318 THEN 'CLR Scalar Function'
          WHEN 21321 THEN 'Inline scalar SQL Function'
          WHEN 21328 THEN 'Partition Scheme'
          WHEN 21333 THEN 'User'
          WHEN 21571 THEN 'Service Broker Service Contract'
          WHEN 21572 THEN 'Trigger on Database'
          WHEN 21574 THEN 'CLR Table-valued Function'
          WHEN 21577
          THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
          WHEN 21581 THEN 'Service Broker Message Type'
          WHEN 21586 THEN 'Service Broker Route'
          WHEN 21587 THEN 'Statistics'
          WHEN 21825 THEN 'User'
          WHEN 21827 THEN 'User'
          WHEN 21831 THEN 'User'
          WHEN 21843 THEN 'User'
          WHEN 21847 THEN 'User'
          WHEN 22099 THEN 'Service Broker Service'
          WHEN 22601 THEN 'Index'
          WHEN 22604 THEN 'Certificate Login'
          WHEN 22611 THEN 'XMLSchema'
          WHEN 22868 THEN 'Type'
          ELSE 'Hmmm???'
        END AS ObjectType
     FROM    [fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                            value
                                                    FROM    [fn_trace_getinfo](NULL)
                                                    WHERE   [property] = 2
                                                  )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.trace_subclass_values v ON v.trace_event_id =   TE.trace_event_id
                                            AND v.subclass_value = t.EventSubClass
          WHERE   TE.name IN ( 'Object:Created', 'Object:Deleted', 'Object:Altered' )
                -- filter statistics created by SQL server                                         
        AND t.ObjectType NOT IN ( 21587 )
                -- filter tempdb objects
        AND DatabaseID <> 2
                -- get only events in the past 24 hours
        AND StartTime > DATEADD(HH, -24, GETDATE())
       ORDERBY t.StartTime DESC ;
    
  • Thank you for your response . I want to query only when a deletion happens and i have so many tables that can be affected by deletion . The most simple way is to have a trigger at the DELETED table but i can not find DELETED table do you know where i can find this table in the SQL SERVER (under which table and which schema ?) . – Java Main Nov 4 '15 at 14:21
  • YOu can read mssqltips.com/sqlservertip/3160/… or use a third party tool ApexSQL Recover (trial version) – KASQLDBA Nov 4 '15 at 14:28
  • Ok that is so detailed solution that should help me to cook mine . Thank you again. – Java Main Nov 4 '15 at 14:54
  • @Java Main, the DELETED table is a virtual table that is exposed only inside a trigger. It has the same schema as the table the trigger is based on. Note that the DELETED table also includes the "old" values for updates so you will need to look at the INSERTED virtual table to determine if it's an update or a true delete. – Jonathan Fite Nov 4 '15 at 15:48
0

Another option is to use Database Auditing to log deletions to the Windows Event Log. You can then attach a task to email you when the event occurs.

-- Create the audit
USE [master];
GO

CREATE SERVER AUDIT [Notify_OS_Log]
TO APPLICATION_LOG
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
);

GO


-- Attach specification to audit
USE My_Database; -- Your db here
GO

CREATE DATABASE AUDIT SPECIFICATION Audit_Deletion_on_MyTable
FOR SERVER AUDIT [Notify_OS_Log]
    ADD (DELETE 
           ON My_Table -- your table here
           BY dbo)
    WITH (STATE = ON);
GO

-- Enable Audit
ALTER SERVER AUDIT [Notify_OS_Log]
WITH (STATE = ON);
GO

In the Windows Task Scheduler, create a new basic task with a name that makes sense (e.g. "DB Deletion Event"). Set it to fire "When an event is logged":

Log: Application
Source: MSSQLSERVER (or MSSQL$SqlInstanceName)
Event ID: 24310

In the Action section, select "Send an Email" and fill in the email details.

This will not use SQLMAIL and you will need access to the host OS in order to set this up, but it should do what you are looking for.

You can use this in conjunction with Change Data Capture, if you need immediate access to the data that changed. However, nothing beats a good backup/restore strategy. If you keep the db in Full Recovery model (and keep a schedule of Full and Log backups) you can use the most recent backup plus a tail-log backup to restore a new copy of the database to the point in time just before the deletion.

Also, an ounce of prevention is worth a pound of cure. You can always remove delete privileges from everyone and only enable them temporarily. It all depends on your situation.

[1] https://msdn.microsoft.com/en-us/library/cc280425(v=sql.105).aspx - Set up an audit

[2] http://blogs.technet.com/b/jhoward/archive/2010/06/16/getting-event-log-contents-by-email-on-an-event-log-trigger.aspx - Use the Event Viewer to create a scheduled task

  • Your method looks simple . I have to try it to check if it works for me . Thanks . – Java Main Nov 4 '15 at 14:55
  • You may have to double check that the event log details are correct for your system. I actually don't have access to my host server to verify, but if my Google-fu is strong it should be right. – Greg Nov 4 '15 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.