Find transaction history

Question

Post reply

Find transaction history

tyang

SSCrazy

Points: 2231
More actions
June 17, 2003 at 7:32 am

#64636

Is there a way to find who modified the table, when the record had been deleted?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi tyang,

quote:
Is there a way to find who modified the table, when the record had been deleted?

are you looking for the user who deleted the record?

You can use CURRENT_USER or USER_NAME() to get this.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

tyang SSCrazy Points: 2231 More actions · Answer 2

Basicly I am looking for the way to view the transaction log.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

quote:
Basicly I am looking for the way to view the transaction log.

you can try log explorer from http://www.lumigent.com

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

tyang SSCrazy Points: 2231 More actions · Answer 4

Can T-SQL do it since third party tool is not possible for me now.

I tried DBCC log(DB name), I don't understand the result.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
I tried DBCC log(DB name), I don't understand the result.

Welcome to the club!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

timfoster Mr or Mrs. 500 Points: 516 More actions · Answer 6

This is something I've been trying to do for a while. The best solution I've come up with is an audit table.

I've looked at writing apps that don't actually delete records, rather they flag them as deleted so that the audit simply needs to store PK, user details, date/time and action but it becomes more hassle that it's worth.

Another option was to write the deleted record to an audit db, but again it became too much aggro.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

LockwoodTech http://www.lockwoodtech.com/index_lognavigator.htm has an item in beta you can download. Might do the job. The output of DBCC LOG isn't extremely complicated just cryptic and takes quit a bit of time to work with. But ty the link and let us know what you think.

JFSHEA SSC Enthusiast Points: 168 More actions · Answer 8

You could add a trigger to the table, that could log the deleted records to an audit trail table.

tyang SSCrazy Points: 2231 More actions · Answer 9

Again since my boss does not want to spend much money on the third party tools, I will try it on my own, but there are too many tables, so it is hard to set the triger on every table.

j.o.s. Ten Centuries Points: 1212 More actions · Answer 10

Either spend a bit of money, or preferably a bit of time, or forget auditing changes to the data. Databases require administration and good design.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

Hi tyang,

quote:
Again since my boss does not want to spend much money on the third party tools, I will try it on my own, but there are too many tables, so it is hard to set the triger on every table.

maybe you should tell your boss, to consider the implicit cost when things go wrong. These could easily outweight the money he thinks to save right now.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

JFSHEA SSC Enthusiast Points: 168 More actions · Answer 12

Triggers are 'free'. That's why I suggested it. I've never done it, but I assume that you could write a stored procedure that could add the triggers to every table in the db, loging all deletes (or whatever you wanted) to a table, for example.