Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer
    
Thank you for your answer. sp_who and sp_who2 only shows current sessions, I need also past ones, such a 'logon history' –  Danilo Brambilla May 7 '10 at 7:53
up vote 0 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.