Monday, June 2, 2008

How to get Client IP Address in SQL Server 2005

Level: Intermediate
Knowledge Required:
  • T-SQL
  • SQL Server 2005
Description:
While executing some query, sometimes it is required to have the Client's IP Address who is executing this Query.
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

We can also put this Query into some function which can be used further.

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
DECLARE
@IP_Address varchar(255);

SELECT @IP_Address = client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;

Return @IP_Address;
END

4 comments:

Anonymous said...

Very useful method, I thought I was going to be limited to HOST_NAME() for a while then.

Arsalan Tamiz said...

Yes but there is a limitation i.e. only sysadmin can use sys.dm_exec_connections, I think this can be handled by using EXECUTE AS but I haven't tested it. I will try to update it as soon as I get some free time.

Anonymous said...

only sysadmin can use sys.dm_exec_connections, so we can't use this if we are logged in with another role who is below sa.

Arsalan Tamiz said...

As per MSDN,

Requires VIEW SERVER STATE permission on the server