Don’t know who dropped the tables in your database, Here you go

Hi Folks – Before some time I came to a situation where somebody dropped so many tables from our database and we had a hard time to find the details. This article will tell you that what were the tables that were dropped and who dropped them.  To illustrate this I’m taking a below example :

/* Use an existing db OR create new one, I’m using existing one */

Use Test_1
go

/* Lets create a test table */

 Create table Test_Table(empID int, name nvarchar(20))

 

/* Insert values in that table */

 insert into Test_Table values (100,’Gaurav’)
insert into Test_Table values (101,’Ashish’)
insert into Test_Table values (102,’Vivek’)

 

/* Check the data in that table */

select * from Test_Table

 

/* Note down the Object Id of that table */

SELECT OBJECT_ID(N’Test_Table’); — In My case object id was 581577110

GO

 

/* Drop that test table */

DROP TABLE [Test_Table];
GO

Everything that we do in db is captured in T-Log and By using below command we can find the transactions that drop tables in the log:

SELECT [Transaction Id], [Begin Time], SUSER_SNAME([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N’DROPOBJ’;
GO

This command will show an output like below :

Pic1

 

 

 

 

Above output tell us who dropped the tables as User (In this case it was gshri) but to know what tables got dropped, pass transaction id in the same function :

SELECT [Lock Information] FROM fn_dblog(NULL, NULL)
WHERE [Transaction Id] = ‘0000:00000343’
AND [Lock Information] LIKE ‘%SCH_M OBJECT%’;

In my case Output was :

Pic2_By_Gaurav_shrivastav

 

 

 

Here 6 shows a database id and 581577110 shows an object id, which is the same that I captured above.

Hope it will help. Thanks.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: