Deleted table log - Need help.
-
Hello All,
I am using SQL-2005 database. I have one database and from that one table was deleted. My client is agreed that the table was deleted from his end but he want to know that who is the user (database user) who has deleted this table.
Please also suggest me best practice to track such kind of malicious activities.
Thanks to all,
Jigar
-
You may be able to find details in the default trace. It's a trace that's always running when SQL is running. Thing it, it's limited in what it stores, 5 files of 20MB each. Books Online will give you details on the trace and where it is.
If the drop table is not in the trace, then there's no way you're going to be able to tell who did it.
Going forward, DDL triggers work great for logging who did what, and you can also set up your own server-side audit trace.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Try checking the 'Schema Changes History' report for that database. It might be having the information you are looking for. Its same as checking default trace output.
Manu
-
On the other hand, you can narrow the field of suspects by checking which user accounts have such privileges on the affected database.
By the way, if application is using a generic ID with dbo privileges any effort to track the offending person down will be futile - assuming you are not allowed to waterboard them to confession 😀
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
jigar_pandya (8/22/2009)
... but he want to know that who is the user (database user) who has deleted this table.Please also suggest me best practice to track such kind of malicious activities.
Actually, best practice is to do your best to prevent this in the first place. Normal users should NOT be able delete tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Where can I find that 'Schema Changes History' report for that database?
I searched everywhere, to no avail.
Thanks
-
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! -
TcW_1978 (12/3/2010)
Where can I find that 'Schema Changes History' report for that database?I searched everywhere, to no avail.
Thanks
See my attachment. do we need set any settings/configuration ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply