InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 03:53:47 GMT20http://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxI am aware of that.However you can deduce who is doing what based on the entries in the default trace.Fri, 28 Dec 2012 14:31:22 GMTarnipeturssonhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspx[quote][b]arnipetursson (12/28/2012)[/b][hr]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.[/quote] 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.Fri, 28 Dec 2012 13:20:16 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxIf 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.Fri, 28 Dec 2012 12:42:55 GMTarnipeturssonhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspx[quote][b]dedicatedtosql (12/26/2012)[/b][hr]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[/quote]Looks to me like you need to set up auditing and CDC in this environment as well.Thu, 27 Dec 2012 06:50:20 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspx[quote][b]krishnarajeesh (12/23/2012)[/b][hr]That is OPERATION 'LOP_DELETE_ROWS' will not have have the login info, where as "LOP_BEGIN_XACT" for that delete will have.[/quote]No, it won't. It has the database user info, not the login info.Thu, 27 Dec 2012 02:37:51 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxThank 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.RegardsWed, 26 Dec 2012 17:59:22 GMTdedicatedtosqlhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxIf 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.Wed, 26 Dec 2012 17:41:58 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxYeah 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. Wed, 26 Dec 2012 17:39:08 GMTdedicatedtosqlhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxSELECT[Current LSN],[Operation],[Transaction ID],[Description], SPID,[Begin Time], [Transaction SID],name 'LoginName'FROM fn_dblog (NULL, NULL),(select sid,name from sys.syslogins) slwhere [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.Tue, 25 Dec 2012 17:44:40 GMTravinder.881986http://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxJust 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 querySELECT [Current LSN], [Operation], [Transaction ID], [Description], SPID,[Begin Time], [Transaction SID], name 'LoginName'FROM fn_dblog (NULL, NULL),(select sid,name from sys.syslogins) slwhere [Transaction Name] LIKE '%delete%' and [Transaction SID] = sl.sid Operation Transaction ID Description SPID Allocunitname nameLOP_BEGIN_XACT 0000:00000207 DELETE;0x01 55 NULL saLOP_BEGIN_XACT 0000:00000215 DELETE;0xdd56d0e1cfe9fd42bafe0aac916518eb 55 NULL testloginLOP_BEGIN_XACT 0000:00000221 DELETE;0x80f4a1243a4e6e439fffe00be23c086a 55 NULL testThis worked for me. Thanks,KrishnaSun, 23 Dec 2012 20:26:09 GMTkrishnarajeeshhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxNope. 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.Thu, 08 Nov 2012 15:28:40 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxI 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.Thu, 08 Nov 2012 15:19:18 GMTdedicatedtosqlhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxPlease don't cross post. It just results in people answering already answered questions.Alspo asked at [url]http://www.sqlservercentral.com/Forums/Topic1382719-1526-1.aspx[/url]Thu, 08 Nov 2012 15:06:06 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspx0x01 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.Thu, 08 Nov 2012 15:05:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspx[quote][b]dedicatedtosql (11/8/2012)[/b][hr]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 [b]::fn_dblog()[/b] 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.[/quote]0x01 is always SA. Not going to provide much in the way of help there I'm afraid.Thu, 08 Nov 2012 15:02:22 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1382711-1550-1.aspxHi 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 [b]::fn_dblog()[/b] 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.Thu, 08 Nov 2012 14:09:52 GMTdedicatedtosql