Auditing a terribly designed database system in SQL 2000

Question

One of the most important databases that I administer is poorly designed. The program which uses it uses only 1 login which happens to be a System Administrator. Edits and deletes are done in place so change tracking is difficult.

Further, it lacks proper auditing functionality so I cannot tell which user edited or deleted a certain record.

I thought I might resort to using SQL triggers and output the record information along with information about the user who edited/deleted it. I can't use LoginName because the program only uses one login.

Can I export other information using a trigger such as the desktop machine name which sent the delete command?

Is there any other information that I could export that would assist me in this?

Answer 1

You might look at the sys.sysprocesses table (sysprocesses in SQL Server 2000 I think). It contains information that you normally see in the output of the sp_who2 stored procedure. It includes hostname, which is the computer that is connecting to SQL Server. Some of the other columns may be useful too.