It’s not often that I’ve had the need to trace back a client to their host computer, but there are time it’s handy. I had thought that the easy way to do this in the past was with sp_who.

However that’s not correct. I checked back in the SQL Server 2000 BOL entry for sp_who and it has hostname, but not IP. That could be the same, but not necessarily.

I saw a post where someone had listed this code

declare @IPAddress as varchar(15)
declare @data XML
set @data = EVENTDATA()
set @IPAddress = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(15)')

That’s interesting. It uses the EVENTDATA(), which tracks data about events. I presume that the IP is in the clienthost all the time, and that it likely comes from the view below, but I can’t find documentation on that. The XSD schema for EventData is beyond me.

I kept thinking this had to be stored in a system table/view somewhere, and sure enough I found sys.dm_exec_sessions. It actually contains the client IP and port, so you can trace things down at a more detailed connection level.

On my local instance it doesn’t seem to work great, but if I connect remotely, I see an IP.