Who dropped the table in SQL Server

14 May,2013 by Jack Vamvas

SQL Server has a default trace feature – a lightweight background trace capturing Object creation, object deletion, error events, auditing events and full text events. For more information read SQL Server – default trace FAQ

If  a user has deleted a database object , such as a table – as long as the trace log is still available – it is easy to write a query and return the relevant information.

The query returns useful information such as Application Name,LoginName,StartTime,ObjectName,Databasename. Enough information to begin a proper analysis of what events led to the object deleted.

I’ve used this query many times , to investigate tables deleted – either accidentally or intentionally. Don’t forget to change the database name .

Once you discover who dropped the table – review the actions and decide on revoking the drop table privileges.Read more on Database Security Countermeasures against hacker attacks

 

SELECT * 
FROM ::fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1

                                                              f.[value]

                                                      FROM    sys.fn_trace_getinfo(NULL) f

                                                      WHERE   f.property = 2

                                                    )), DEFAULT) tf
INNER JOIN sys.trace_events te
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS tc
ON te.category_id = tc.category_id
WHERE databasename = 'MY_DB' AND
objectname IS NOT NULL AND 
te.category_id = 5 AND 
te.trace_event_id = 47  
AND tf.StartTime > getdate() -1

 Read More

How to create a SQL Server Security Audit - SQL Server DBA

Powershell sql server security audit - SQL Server DBA

SQL Server – Last DML operation - SQL Server DBA



Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Who dropped the table in SQL Server


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer