How to find out when a table was dropped, and by which user?

Question

How to find out when a table was dropped, and by which user?

CH&HU-1021641

Ten Centuries

Points: 1387
More actions
March 2, 2009 at 2:30 am

#126745

Dear All,
Kindly let me know is there anyway to track the user who has deleted the TABLE from SQL Server 2005.
Issues : In a database we have many number of tables. Among them 2 tables were deleted / dropped and the server was rebooted.
How can i trace the user now ?
Thanks,
CH&HU..

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Providing the default trace is running, and the table wasn't dropped too long ago, you can use that.

The default trace is 5 trace files of 20MB apiece that are put into the SQL error log directory. You can either open them with SQL profiler or use the fn_trae_getdata function to query them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CH&HU-1021641 Ten Centuries Points: 1387 More actions · Answer 2

Thanks for the reply Gail,

I used :

'SELECT * FROM ::fn_trace_gettable ('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_90.trc', default)'. Result : StartTime of server : "2009-03-01 01:34:13.780" and the rest alla are Null values.

But i can see the data which is after the reboot (StartTime), but i need the information of before recycle. And one more update here - the 2 tables were dropped. I tried alot keeping the whole day. But no use till this point of time. it is going to be a great issue as the tables dropped in weekends i.e., yesterday in Production. Could you please help me out if i can have another way to trace...

Thanks,

CH&HU.

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 3

As per our sql_central articles

U can use DDl trigger for maintains the table activity

use DB_name

CREATE TABLE create_table_log

( create_time datetime

, DB_User nvarchar(100)

, Event_type nvarchar(100)

, TSQL nvarchar(2000));

use Db_name

go

create trigger trig_create_table on database for create_table

as

Declare @data xml

set @data=Eventdata()

Insert into create_table_log values

(getdate(),

@data.value('(/EVENT_INSTANCE/LOGINNAME)[1]','nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/EVENTTYPE)[1]','nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(200)')

)

now u can see the table "create_table_log " and finout..ok

if u want to protect the db u can use another DDl Trigger like this

CREATE TRIGGER protection

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT 'U HAVE NOT A PERMISSION,PLEASE CONTACT SARAVANAN ZEETAA'

ROLLBACK

If u satisfied ..please reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

cshekhar (3/2/2009)
But i can see the data which is after the reboot (StartTime), but i need the information of before recycle.

There should be 5 trace files. What about the others?

Could you please help me out if i can have another way to trace...

If it's not in the trace, you're out of luck. There's no other way to get historical info. For the future, you can consider DDL triggers.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 5

there's a script contribution that would load the default trace into a table for review:

Load All 5 Default Trace Files[/url]

maybe that will help

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

CH&HU-1021641 Ten Centuries Points: 1387 More actions · Answer 6

Thanks much Saravanan,

Its working.. 🙂

But can i have query to drop a table when i would like to alter or drop, in some cases during deployement we will have to drop and recreate some of the tables.

If we use this Trigger its working 100% to not to drop. But ineed to break this for some times. Can i have that as well please ?

Simply i need to remove this protection in some cases, for that scenario how can i ?

Thanks,

CH&HU,

INDIA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

cshekhar (3/2/2009)
Simply i need to remove this protection in some cases, for that scenario how can i ?

Disable the trigger.

A better approach than a DDL trigger would be to ensure that users who aren't allowed to drop tables don't have the rights to drop tables.

The ddl trigger is then just a last line of protection against careless DBAs

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CH&HU-1021641 Ten Centuries Points: 1387 More actions · Answer 8

Dear Gail,

The result is as i have mentioned earlier. I agree with your ans that i couldn't trace the user...

Trying to make sure that this will not repeat in future.

Thanks for your inputs.

Regards,

CH&HU.

rajganesh.dba SSCrazy Points: 2538 More actions · Answer 9

Just a word to this discussion , run this query select * from ::fn_trace_getinfo(0) and find out how many trace files are running .If only the default audit , try configuring a new audit with all your requirement so ur environment can be safe under monitoring

CH&HU-1021641 Ten Centuries Points: 1387 More actions · Answer 10

Dear All,

Thanks for your inputs..

Could you please let me know how can i protect every object in a database as SPs, Views etc..

Thanks,

CH&HU.

CH&HU-1021641 Ten Centuries Points: 1387 More actions · Answer 11

Dear Gail / All,

Please find the results found for the query ran : select * from ::fn_trace_getinfo(0)

traceid property value

112

12C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_91.trc

1320

14NULL

151

i couldn't undertand by the above result as i have already ran using the above path and the result was also posted earlier.

Thanks,

CH&HU.