Determine which user deleted a SQL Server database?

Question

I have a SQL Server 2005 database that has been deleted, and I need to discover who deleted it. Is there a way of obtaining this user name?

Thanks, MagicAndi.

Solution 1

If there has been little or no activity since the deletion, then the out-of-the-box trace may be of help. Try running:

DECLARE @path varchar(256)

SELECT @path = path
FROM sys.traces
where id = 1

SELECT *
FROM fn_trace_gettable(@path, 1)

[In addition to the out-of-the-box trace, there is also the less well-known 'black box' trace, which is useful for diagnosing intermittent server crashes. This post, SQL Server’s Built-in Traces, shows you how to configure it.]

Solution 2

I would first ask everyone who has admin access to the Sql Server if they deleted it.

Solution 3

The best way to retrieve the information is to restore the latest backup.

Now to discuss how to avoid such problems in the future.

First make sure your backup process is running correctly and frequently. Make transaction log baclup evey 15 mintues or half an hour if it is a higly transactional database. Then the most you lose is a half an hour's worht of work. Practice restoring the database until you can easily do it under stress.

In SQL Server 2008 you can add DDL triggers (not sure if you can do this in 2005) which allow you to log who did changes to structure. It might be worth your time to look into this.

Do NOT allow more than two people admin access to your production database - a dba and a backup person for when the dba is out. These people should load all changes to the database structure and code and all of the changes should be scripted out, code reviewed and tested first on QA. No unscripted, "run by the seat of your pants" code should ever be run on prod.

Solution 4

Here is bit more precise TSQL

SELECT DatabaseID,NTUserName,HostName,LoginName,StartTime
FROM 
sys.fn_trace_gettable(CONVERT(VARCHAR(150), 
        ( SELECT TOP 1
                    f.[value]
            FROM    sys.fn_trace_getinfo(NULL) f
            WHERE   f.property = 2
        )), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE TE.trace_event_id =47 AND T.DatabaseName = 'delete'
-- 47 Represents event for deleting objects. 

This can be used in the both events of knowing or not knowing the database/object name. Results look like this:

View more solutions

Comments

[Tangiest]

HLGEM - Good answer, shame it wasn't to the question I asked. -1

[Wadih M.]

+1 - Thanks, that's very useful. I just ran that and it gives me about 2 days worth of backups. Is there a way to get more? What options take care of controlling those trace settings?

[KM.]

+1, this is the answer to the question that you needed to ask before this current question: "how do I prevent and/or deal with someone deleting my database"...

[Raj More]

@mitch: that is an awesome tip. it is a very small trace, though. is there any way i can set this up to be a larger trace?

[John Sansom]

+1: An excellent answer due to thinking laterally in order to identify the root underlying cause of the issue I.E. a failing due to lack of database platform auditing and management processes.

[dburges]

@MagicAndi, I aswered part of what you asked which is how to retreive the information. I added the other information because, it may not be possible to get back what was lost if you weren't doing backups, at least you can go to your boss with a plan to avoid a repetition. It might be thing that saves your job if you can't find a way to determine who did the deleting. Sometimes you have provide people with what they need to know or consider not just what they asked.

[Mitch Wheat]

@Tapori: I blogged about SQL Server’s Built-in Traces here: mitch-wheat.blogspot.com/2009/01/… : it's possible to alter the size of the Black Box trace...

[Tangiest]

HLGEM, I would make two points. 1. You did NOT answer the question asked, i.e. how to identify the user who deleted the database in the first place? How will restoring the last backup do this? 2. If you had wanted to answer a question about preventing this problem occurring again, you should have asked this as a separate question, and linked to this question in your answer to my question. Lateral thinking or not, you were answering a different question; surely the point to SO is to provide answers to specific questions?

[dburges]

you asked and I quote: "Is there a way of retrieving this information?" I answered that part of the question. Restoring the backups is how to do that. You can be sure I won't be answering any more of your questions since you clearly are incapable of accepting that the information you may need isn't the necessarily the direct answer to your question. BTW two other people agreed my answer was appropriate.

[Tangiest]

HLGEM, Given that you never answered the question that I had asked in the first place, I am sure I can live without you answering any more of my questions in the future.

[stephan]

And if I had to ask more than three people, I would also ask the DBA why there are so many...

[Matt Wilko]

+1 I know the answer is not related to the OPs question, but just because of the "Practice restoring the database until you can easily do it under stress" I will up-vote it!