November 8, 2012 at 2:09 pm
Hi All,
Recently some one deleted some rows from a table. I was asked to find out who did it. Since the log has not been backed up since the time the DB was created I took the help of undocumented Table valued function ::fn_dblog() which gives me the contents of the active portion of the log.
I filtered on AlocUnitName and operation column.
Allocunitname being the table name and OPERATION being the 'LOP_DELETE_ROWS'.
I was looking fior the column TRANSACTION SID to find out the SID of the user that started the transaction that deleted the rows. I did get it.
But the problem is the value of the SID is 0x01 which is the dbo user. It is evident that a server level login with sysadmin privilages did the delets. Is there any way I can find out the server login mapped to the dbo user?
Any idea would be appriciated.
November 8, 2012 at 3:02 pm
dedicatedtosql (11/8/2012)
Hi All,Recently some one deleted some rows from a table. I was asked to find out who did it. Since the log has not been backed up since the time the DB was created I took the help of undocumented Table valued function ::fn_dblog() which gives me the contents of the active portion of the log.
I filtered on AlocUnitName and operation column.
Allocunitname being the table name and OPERATION being the 'LOP_DELETE_ROWS'.
I was looking fior the column TRANSACTION SID to find out the SID of the user that started the transaction that deleted the rows. I did get it.
But the problem is the value of the SID is 0x01 which is the dbo user. It is evident that a server level login with sysadmin privilages did the delets. Is there any way I can find out the server login mapped to the dbo user?
Any idea would be appriciated.
0x01 is always SA. Not going to provide much in the way of help there I'm afraid.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2012 at 3:05 pm
0x01 as a user sid is DBO, that's the user mapped to all sysadmin logins, sa and any other member of the sysadmin role. The log does not contain login sids, just database user sids.
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
November 8, 2012 at 3:06 pm
Please don't cross post. It just results in people answering already answered questions.
Alspo asked at http://www.sqlservercentral.com/Forums/Topic1382719-1526-1.aspx
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
November 8, 2012 at 3:19 pm
I am sorry for the repost. I will make point that I will not do it future. The reason I did that was since it was security question I wanted to do there as well.
So coming to the issue there is no way to track thye dbo back to thr login with sysadmin privilages right? No other column returned by ::fn_dblog() helps in tracking it back.
Any way thanks for the help.
November 8, 2012 at 3:28 pm
Nope. All that's in the log is the user id. The transaction log is not an audit log. Rollbacks and database recovery do not require any information on the login, host, app or any other such information.
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
December 23, 2012 at 8:26 pm
Just try searching for [Transaction Name] LIKE '%delete%'.
That is OPERATION 'LOP_DELETE_ROWS' will not have have the login info, where as "LOP_BEGIN_XACT" for that delete will have.
Sample query
SELECT
[Current LSN],
[Operation],
[Transaction ID],
[Description], SPID,[Begin Time], [Transaction SID],
name 'LoginName'
FROM fn_dblog (NULL, NULL),
(select sid,name from sys.syslogins) sl
where [Transaction Name] LIKE '%delete%' and [Transaction SID] = sl.sid
Operation Transaction ID Description SPIDAllocunitnamename
LOP_BEGIN_XACT0000:00000207DELETE;0x01 55NULL sa
LOP_BEGIN_XACT0000:00000215DELETE;0xdd56d0e1cfe9fd42bafe0aac916518eb55NULL testlogin
LOP_BEGIN_XACT0000:00000221DELETE;0x80f4a1243a4e6e439fffe00be23c086a55NULL test
This worked for me.
Thanks,
Krishna
December 25, 2012 at 5:44 pm
SELECT
[Current LSN],
[Operation],
[Transaction ID],
[Description], SPID,[Begin Time], [Transaction SID],
name 'LoginName'
FROM fn_dblog (NULL, NULL),
(select sid,name from sys.syslogins) sl
where [Transaction Name] LIKE '%delete%' and [Transaction SID] = sl.sid
this query is not showing any results though the rows got deleted from the table. I have few rows from the table and checked it. Its not giving any results with details who has deleted them.
Can you please help on this.
December 26, 2012 at 5:39 pm
Yeah I did the same thing. But the problem here is the SID was showing 0x01 which is a dbo user. i.e.. a sysadmin mapped to dbo with have SID 0x01. SO it is has not possible(to my knowledge) to get which login performed the delete.
December 26, 2012 at 5:41 pm
If it is important to know who did something, you should look at setting up auditing so that you can capture this in the future. You may also want to look at CDC if you need to capture the actual changes to data.
December 26, 2012 at 5:59 pm
Thank you very much for the advice.
Actualy We have both CDC as well as Auditing in place for the prod database. But this was a local environment. Where we have many sysadmins. I know it is a worst practice. I am new here and I adviced them not to. But they want it to stay this way.
Regards
December 27, 2012 at 2:37 am
krishnarajeesh (12/23/2012)
That is OPERATION 'LOP_DELETE_ROWS' will not have have the login info, where as "LOP_BEGIN_XACT" for that delete will have.
No, it won't. It has the database user info, not the login info.
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
December 27, 2012 at 6:50 am
dedicatedtosql (12/26/2012)
Thank you very much for the advice.Actualy We have both CDC as well as Auditing in place for the prod database. But this was a local environment. Where we have many sysadmins. I know it is a worst practice. I am new here and I adviced them not to. But they want it to stay this way.
Regards
Looks to me like you need to set up auditing and CDC in this environment as well.
December 28, 2012 at 12:42 pm
If you have default trace records from around the time of the delete, you may be able to compile a list of suspects. Hopefully you do not too may people that have sysadmin access on your system.
December 28, 2012 at 1:20 pm
arnipetursson (12/28/2012)
If you have default trace records from around the time of the delete, you may be able to compile a list of suspects. Hopefully you do not too may people that have sysadmin access on your system.
That won't help, I'm afraid.
the default trace captured DDL changes..CREATE TABLE/INDEX etc kinds of things.
it does not capture any DML statements like INSERT/UPDATE/DELETE; for that you need a different custom trace set up prior to the changes occurring to get any relevant info from any trace.
Lowell
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply