YVCC160
asked on
Can I find out "who" deleted a table in a SQL 2005 database from a log or script of some kind?
Between 6/18 and 9/18 someone has deleted a table on the SQL 2005 server. I have the data to restore the table but I'm more concerned with who deleted it. Is there a way to find out?
jogos
Concerned because you don't want it to happen again? Then you should restrict the users so they can't do that anymore.
It is not possible for the past. For the future you can create DDL Triggers.
See this link for such a tool and what you can do with it
http://www.red-gate.com/products/solutions_for_sql/viewing_transaction_history.htm
The say
"SQL Log Rescue is ideal for protecting your data in the event of mistakes or accidents, recovering dropped tables, undoing rogue queries, and identifying who has done what to your database, and when they did it. As a disaster recovery tool it is invaluable, since it will minimize data loss as well as downtime."
Be aware that the 'WHO' will be a database-user and when other trace-info don't identify the person you can end up with a whole department.
http://www.red-gate.com/products/solutions_for_sql/viewing_transaction_history.htm
The say
"SQL Log Rescue is ideal for protecting your data in the event of mistakes or accidents, recovering dropped tables, undoing rogue queries, and identifying who has done what to your database, and when they did it. As a disaster recovery tool it is invaluable, since it will minimize data loss as well as downtime."
Be aware that the 'WHO' will be a database-user and when other trace-info don't identify the person you can end up with a whole department.
ASKER
thanks for the quick response. I'm going to close the question.
jogos,
>>See this link for such a tool and what you can do with it<<
You may want to double check that. What you have not stated is that is only compatible with SQL Server 2000. Since the author appears to be using SQL Server 2005, that would make it a dead-end, at least until Red-Gate can resolve the licensing problems they have with Microsoft.
>>See this link for such a tool and what you can do with it<<
You may want to double check that. What you have not stated is that is only compatible with SQL Server 2000. Since the author appears to be using SQL Server 2005, that would make it a dead-end, at least until Red-Gate can resolve the licensing problems they have with Microsoft.
@acperkins,
indeed I overlooked the SQL2005.... but point was 'it's possible but it will take you a tool ...and you don't have guarantee when you don't have proper credentials'. And then we are back where I started: 'restrict access'.
indeed I overlooked the SQL2005.... but point was 'it's possible but it will take you a tool ...and you don't have guarantee when you don't have proper credentials'. And then we are back where I started: 'restrict access'.
Absolutely. Something about "closing the barn door after the horse escapes" comes to mind.
ASKER
Well the tables are restricted except for a few developers. I was more interested in finding out if this could be related to something they were doing. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry I guess I picked the wrong way to close the question. I really do appreciate your help. Thanks
OK