Knowledge Required:
- T-SQL
- SQL Server 2005
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:
Very useful method, I thought I was going to be limited to HOST_NAME() for a while then.
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.
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.
As per MSDN,
Requires VIEW SERVER STATE permission on the server
Post a Comment