Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters).

I thought this could be found from the transaction log (providing it hadn't been truncated) - is this correct? And if so how do you actually go about finding this information out?

share|improve this question
add comment

3 Answers

up vote 18 down vote accepted

I've not tried fn_dblog on Express but if it is available the following will give you delete operations:

SELECT 
    * 
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

Take the transaction ID for transactions you're interested in and identify the SID that initiated the transaction with:

SELECT
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = @TranID
AND
    [Operation] = 'LOP_BEGIN_XACT'

Then identify the user from the SID:

SELECT
    *
FROM 
    sysusers
WHERE
    [sid] = @SID

Edit: Bringing that all together to find deletes on a specified table:

DECLARE @TableName sysname
SET @TableName = 'dbo.Table_1'

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM 
        fn_dblog(NULL, NULL) 
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]
share|improve this answer
    
This does indeed work with SQL express but on my system it only shows transactions that happened today. I didn't think SQL Express had an transaction log truncation out of the box? –  Matt Wilko Aug 3 '11 at 16:00
3  
If your database is in simple recovery model, you can't make any assumptions about how long inactive transactions will stick around in the log. –  Aaron Bertrand Aug 3 '11 at 16:27
2  
Transaction log is fundamental, rather than optional. What's the recovery model for the database (simple or full) and how are backups configured (full only or log backup + full)? –  Mark Storey-Smith Aug 3 '11 at 16:28
    
Amazingly Great....really was not aware of this...Has helped me...Thank you once again... –  user15056 Nov 5 '12 at 12:39
    
I stole this for my answer here though refactored a bit to avoid the self join on fn_dblog. One downside is that it returns the database USERNAME() rather than the much more useful login name. –  Martin Smith Dec 13 '13 at 18:15
add comment

If database is in full recovery mode or if you have transaction log backups you can try to read these using third party log readers.

You can try ApexSQL Log (premium but has a free trial) or SQL Log Rescue (free but sql 2000 only).

share|improve this answer
add comment

how they could find out who deleted some data in their SQL Server database

Although this is answered, wanted to add that SQL Server has a default trace enabled and it can be used to find out who dropped/altered the objects.

Object events

Object events include: Object Altered, Object Created and Object Deleted

note: SQL Server by default has 5 trace files, 20 MB each and there is no known supported method of changing this. If you have a busy system, the trace files may roll over far too fast (even within hours) and you may not be able to catch some of the changes.

Excellent example can be found : The default trace in SQL Server - the power of performance and security auditing

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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