IP adress for SPID ?

Question

IP adress for SPID ?

k.przewozniak

Ten Centuries

Points: 1133
More actions
February 17, 2009 at 8:15 am

#126479

Hi All,
How I can get the IP adress of the host from which login is connecting to my database in SQL server?
Using sp_who2 I can see SPID, login, to with database is connected. However this login can be used but many users, and I would like to know from with IP is this connection. Hostname is not specified, also there is nothing in ProgramName
Thank you for your help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

it's possible to connect to SQL server without using TCP/IP (you can use named pipes); so those named pipes users would not have an IP address available to query;

Just like you already identified, I've always tried to use HOSTNAME to try and get an IP Address....I think you might be out of luck on that.

here's what i've used in the past:

[font="Courier New"]

SELECT

spid,

hostname,

program_name,

DB_NAME(dbid) AS dbname,

CONVERT(SYSNAME, RTRIM(loginame)) AS loginname,

CONVERT(VARCHAR(20),'') AS IPAddress

INTO #myspids

FROM MASTER.dbo.sysprocesses (nolock)

WHERE hostname <> ''

DECLARE

@spid INT,

@hostname VARCHAR(64),

@ip VARCHAR(20)

DECLARE c1 CURSOR FOR SELECT spid,hostname FROM #myspids

OPEN c1

FETCH next FROM c1 INTO @spid,@hostname

WHILE @@fetch_status <> -1

BEGIN

CREATE TABLE #tbl (output VARCHAR(255))

INSERT #tbl

EXEC xp_cmdShell 'nslookup DAISY|find "Address"'

SELECT @ip= LTRIM(RTRIM(REPLACE(output,'Address:' ,''))) FROM #tbl WHERE output LIKE 'Address:%'

UPDATE #myspids SET IPAddress = @ip WHERE spid=@spid

DROP TABLE #tbl

FETCH next FROM c1 INTO @spid,@hostname

END

CLOSE c1

DEALLOCATE c1

SELECT * FROM #myspids

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!