<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Using ::fn_dblog() to find who deleted the...
16 posts, Page 1 of 2
1
2
»»
Using ::fn_dblog() to find who deleted the rows in a table.
Rate Topic
Display Mode
Topic Options
Author
Message
dedicatedtosql
dedicatedtosql
Posted Thursday, November 08, 2012 2:09 PM
Grasshopper
Group: General Forum Members
Last Login: 2 days ago @ 12:55 PM
Points: 14,
Visits: 100
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.
Post #1382711
Sean Lange
Sean Lange
Posted Thursday, November 08, 2012 3:02 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 11:58 AM
Points: 8,600,
Visits: 8,240
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 Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1382732
GilaMonster
GilaMonster
Posted Thursday, November 08, 2012 3:05 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713,
Visits: 29,968
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 2008, MVP
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
Post #1382733
GilaMonster
GilaMonster
Posted Thursday, November 08, 2012 3:06 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713,
Visits: 29,968
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 2008, MVP
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
Post #1382735
dedicatedtosql
dedicatedtosql
Posted Thursday, November 08, 2012 3:19 PM
Grasshopper
Group: General Forum Members
Last Login: 2 days ago @ 12:55 PM
Points: 14,
Visits: 100
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.
Post #1382742
GilaMonster
GilaMonster
Posted Thursday, November 08, 2012 3:28 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713,
Visits: 29,968
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 2008, MVP
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
Post #1382747
krishnarajeesh
krishnarajeesh
Posted Sunday, December 23, 2012 8:26 PM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 10,
Visits: 485
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 SPID Allocunitname name
LOP_BEGIN_XACT 0000:00000207 DELETE;0x01 55 NULL sa
LOP_BEGIN_XACT 0000:00000215 DELETE;0xdd56d0e1cfe9fd42bafe0aac916518eb 55 NULL testlogin
LOP_BEGIN_XACT 0000:00000221 DELETE;0x80f4a1243a4e6e439fffe00be23c086a 55 NULL test
This worked for me.
Thanks,
Krishna
Post #1399788
ravinder.881986
ravinder.881986
Posted Tuesday, December 25, 2012 5:44 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, March 06, 2013 10:25 PM
Points: 6,
Visits: 81
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.
Post #1400108
dedicatedtosql
dedicatedtosql
Posted Wednesday, December 26, 2012 5:39 PM
Grasshopper
Group: General Forum Members
Last Login: 2 days ago @ 12:55 PM
Points: 14,
Visits: 100
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.
Post #1400421
Lynn Pettis
Lynn Pettis
Posted Wednesday, December 26, 2012 5:41 PM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 21,607,
Visits: 27,438
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.
Lynn Pettis
For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here
or
when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here
and
here
Managing Transaction Logs
SQL Musings from the Desert
Fountain Valley SQL
(My Mirror Blog)
Post #1400422
« Prev Topic
|
Next Topic »
16 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
