Possible to determine machine_name and windows username executing a query on SQL Server (2000)?

We have a huge amount of diverse desktop apps, spreadsheets (1500+), etc accessing a SQL Server. We would like to start logging on the server that will tell us as much as possible about which users, on what machines, are executing what sql, from what applicat开发者_StackOverflow社区ion (windows exe/process name). Specifically I am interested in machine name and username, but would like as much information as is available.

Is this possible?

Important: I am interested in general, but my current situation is SQL Server 2000.

---

To inspect the current state:

In SQL 2000 you would look in sysprocesses:

• hostname Name of the workstation.
• program_name Name of the application program.

SQL 2005 and after you look in sys.dm_exec_sessions

• host_name Name of the client workstation that is specific to a session. The value is NULL for internal sessions. Is nullable.
• program_name Name of client program that initiated the session. The value is NULL for internal sessions. Is nullable.

To log this information you would use SQL tracing, trace the SQL:BatchCompleted and RPC:Completed events. Tracing, even when done on the server side, adds significant overhead and is very very unlikely you'll be able to afford it without significant performance degradation. See Monitoring Events.

---

An alternative and possibly superior approach to this may be to use Microsoft Network Monitor, filtered on the SQL Server port.

Download
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=983b941d-06cb-4658-b7f6-3088333d062f&displaylang=en

How to capture network traffic with Network Monitor
http://support.microsoft.com/kb/148942