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?
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.