I found something which might work for you
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
From How to get Client IP Address in SQL Server
Also have a look at this article about Get client IP address
You can try out this solution. It even works on shared hosting:
select CONNECTIONPROPERTY('client_net_address') AS client_net_address
it needs just single line of code
SELECT CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))
Ultimately join the two system tables:
SELECT hostname,
net_library,
net_address,
client_net_address
FROM sys.sysprocesses AS S
INNER JOIN sys.dm_exec_connections AS decc ON S.spid = decc.session_id
WHERE spid = @@SPID
Output:
hostname | net_library | net_address | client_net_address
PIERRE | TCP/IP | 0090F5E5DEFF| 10.38.168.5
I couldn't get the exact numeric IP address, instead I got a NULL value because of the limitation of the above statements. The limit is that you only get IP addresses if you're connected via TCP/IP. If you're local and using Shared Memory then those attributes don't exist. If you turn off Shared Memory (or any protocols except for TCP/IP) via Server Configuration Manager you will always get IP address for any connection.
You are best stuck with
SELECT SERVERPROPERTY(N'MachineName');
... which can act in place of numeric IP address.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With