SQL Server 2000/2005 query list user access

Question

is there a query to list database user access and related source IPs on SQL Server 2000 and 2005? Please note that reporting service is not installed.

Thank you all for help.

Answer 1

sp_who or sp_who2 should give you what you need. They won't list the ip address but they will list the hostname. sp_who2 will give you more information than sp_who.

Answer 2

I found this query that seems to list user access but it doesn't record source IP. Please note path has to be verified before runnning the query:

SELECT TargetLoginName, SessionLoginName, HostName, StartTime
FROM fn_trace_gettable('X:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_59.trc', default);
GO