August 8, 2008 at 3:47 am
Hi All,
I need a query to know who deleted my Table..from my database.
Cheers!
Sandy.
--
August 8, 2008 at 4:07 am
Sandy (8/8/2008)
Hi All,I need a query to know who deleted my Table..from my database.
Cheers!
Sandy.
Well, unless you were running a trace, it is not too easy to figure out who did this. One way (I assume you are running on SQL Server 2005 is:
Run
dbcc log (mydatabaseid,-1) with tableresults
(replace the mydatabaseid with the database id of the database you need (select db_id())
Find the rows where the Operation is LOP_DELETE_ROWS and the AllocUnitName is sys.sysschobjs.clst
These are object drop operations.
look at the Transaction ID column, and find the previous LOP_BEGIN_XACT (operation is LOP_BEGIN_XACT) row, and see the UID column. If the column is -1, then the uid is not recorded, if it is not -1, then you have the person who did this.
The above is not the easiest way of finding out who did this to your table, and the transaction log is not an audit log. Have a look at who has permissions to drop your table, and then maybe ask who did this 🙂
Regards,
Andras
August 8, 2008 at 4:10 am
SQL 2005 has a default trace running which you can use.
An even easier option is the "Schema changes History" report in SSMS.
[font="Verdana"]Markus Bohse[/font]
August 8, 2008 at 4:28 am
Andras,
Thanks, but my question was who drop my table instead of delete the data, Is DDL command logged in transaction table?
we can also achieve this by another command too like this...,
use testdb
go
select * from abc
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
Please tell me how to know who drop my table????
Cheers!
Sandy.
--
August 8, 2008 at 4:30 am
Sandy (8/8/2008)
Andras,Thanks, but my question was who drop my table instead of delete the data, Is DDL command logged in transaction table?
we can also achieve this by another command too like this...,
use testdb
go
select * from abc
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT'
Please tell me how to know who drop my table????
Cheers!
Sandy.
Hi Sandy,
when you drop a table the system tables are modified, and the table drop will result in a delete I described. However, I've completely forgotten that the default trace records table drops, so ignore my message, and follow Markus's advice.
Regards,
Andras
August 8, 2008 at 4:34 am
Andras,
If in Sql Server 2000, then...
Cheers!
Sandy.
--
August 8, 2008 at 4:41 am
Sandy (8/8/2008)
Andras,If in Sql Server 2000, then...
Cheers!
Sandy.
Now, 2000 is probably when my solution above is actually relevant (2000 does not have a default trace).
On 2000 there is no with tableresult, so to get the log you need to:
dbcc traceon(3604)
dbcc log (databaseid, -1)
dbcc traceoff(3604)
(replace databaseid with the relevant databas id)
Look for LOP_DELETE_ROWS for the dbo.sysobjects table.
On 2005, as Marcus mentioned, the default trace will give you easier access to the table drop information. On 2000 however, you may want to look at free third party tools that can dig this information out for you.
Regards,
Andras
August 11, 2008 at 12:20 am
Thanks..........:),
Cheers!
Sandy.
--
August 11, 2008 at 1:18 am
Just be aware that the info is only in the log until the next checkpoint (if on simple recovery) or log backup (on full or bulk-logged recovery)
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
August 11, 2008 at 3:29 am
Yes Gail,
Thats true. I do agree but do you have any alternative for this?
Cheers!
Sandy.
--
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply