title: How to get a client IP address from an SQL Server database
body: <p>Suppose I have three machines. One is server and the other two are clients. An SQL database has been installed on the server and an SQL&nbsp;Server client is installed on the other two machines. So people can connect to the SQL&nbsp;Server server database from their client machine.</p>
  
  <p>Now I want, whenever anybody do the DML operation on table, then a trigger will fire and from that trigger the user's client machine IP address will be stored in another table. So I search good and found a script which is:</p>
  
  <pre><code>create Procedure sp_get_ip_address (@ip varchar(40) out)
  as
  begin
      Declare @ipLine varchar(200)
      Declare @pos int
      set nocount on
      set @ip = NULL
      Create table #temp (ipLine varchar(200))
      Insert #temp exec master..xp_cmdshell 'ipconfig'
      select @ipLine = ipLine
      from #temp
      where upper (ipLine) like '%IP ADDRESS%'
      if (isnull (@ipLine,'***') != '***')
      begin
          set @pos = CharIndex (':',@ipLine,1);
          set @ip = rtrim(ltrim(substring (@ipLine ,
          @pos + 1 ,
          len (@ipLine) - @pos)))
      end
      drop table #temp
      set nocount off
  end
  go
  
  declare @ip varchar(40)
  exec sp_get_ip_address @ip out
  print @ip
  </code></pre>
  
  <p>But this above script gives a server IP address where the database has been installed. I am not looking for this script. I got another script which works fine. The script is:</p>
  
  <pre><code>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
  
  SELECT dbo.GetCurrentIP()
  </code></pre>
  
  <p>More scripts</p>
  
  <hr>
  
  <pre><code>DECLARE @host varchar(255)
  SET @host = host_name()
  
  CREATE TABLE #Results (
  Results varchar(255)
  )
  
  DECLARE @cmd varchar(260)
  SET @cmd = 'ping ' + @host
  
  INSERT INTO #Results
  EXEC master..xp_cmdshell @cmd
  
  SELECT Replace(Left(Results, CharIndex(']', Results)), 'Pinging ', '') As [client]
   , host_name() As [host_name()]
  FROM   #Results
  WHERE  Results LIKE 'Pinging%'
  
  DROP TABLE #Results
  </code></pre>
  
  <p>--*********************************************</p>
  
  <pre><code>-- DROP trigger and/or table if they exist
  IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'TR' AND name = 'myTable_InsertUpdate') BEGIN
      DROP TRIGGER myTable_InsertUpdate
  END
  IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'myTable') BEGIN
      DROP TABLE myTable
  END
  
  -- Create out table; note the audit fields
  CREATE TABLE myTable (
  id int PRIMARY KEY NOT NULL IDENTITY(1,1)
  , field1           char(1)
  , changed_by_ip    char(15)
  , changed_by_host  char(15)
  , datetime_changed datetime
  )
  GO
  
  -- Create trigger for update and insert
  CREATE TRIGGER myTable_InsertUpdate
  ON  myTable
  FOR insert, update
  AS
  DECLARE @ipLine varchar(255)
  DECLARE @pos    int
  DECLARE @ip     char(15)
  
  -- Temporary table creation
  CREATE TABLE #ip (
  ipLine varchar(255)
  )
  
  -- Insert the return of ipconfig into the temp table
  INSERT #ip EXEC master..xp_cmdshell 'ipconfig'
  
  -- Find the line which contains the IP address and assign it to a variable
  SET @ipLine = (
  SELECT ipLine
  FROM   #ip
  WHERE  ipLine LIKE '%IP Address%'
  )
  
  -- If the IP address is known
  IF Coalesce(@ipLine, '***') &lt;&gt; '***' BEGIN
      --Find the index of the colon from the END of the string
      SET @pos = CharIndex(':', Reverse(@ipLine), 1) - 1
      --Trim the IP off the end of the string
      SET @ip =  Right(@ipLine, @pos)
      --Remove any trailing or leading white space
      SET @ip  = RTrim(LTrim(@ip))
  END
  
  -- Drop the temp table
  DROP TABLE #ip
  
  -- Update the audit fields based on the value being updated
  UPDATE myTable
  SET    changed_by_ip  = @ip
     , datetime_changed = GetDate()
     , changed_by_host  = host_name()
  WHERE  id IN (SELECT id FROM inserted)
  GO
  
  -- Insert some test values
  INSERT INTO myTable (field1) VALUES ('a')
  INSERT INTO myTable (field1) VALUES ('a')
  
  -- Display initial values
  SELECT * FROM myTable
  
  -- Update one of the fields
  UPDATE myTable
  SET    field1 = 'b'
  WHERE  id = 2
  
  -- Display changed values.
  SELECT * FROM myTable
  
  -- Notice the change in datetime_changed where id = 2
  GO
  
  -- And finally; clean up after ourselves
  DROP TRIGGER myTable_InsertUpdate
  DROP TABLE myTable
  </code></pre>
  
  <p>This works on my install of <a href="https://en.wikipedia.org/wiki/History_of_Microsoft_SQL_Server#SQL_Server_2000" rel="nofollow noreferrer">SQL Server 2000</a> and <a href="https://en.wikipedia.org/wiki/History_of_Microsoft_SQL_Server#SQL_Server_2005" rel="nofollow noreferrer">SQL Server 2005</a>.</p>
  
  <ol>
  <li><p>Can I call this GetCurrentIP() function from trigger?</p></li>
  <li><p>Will the GetCurrentIP() function be compatible for all SQL&nbsp;Server versions? For example, SQL Server 2000</p></li>
  <li><p>If any user connects to the database and does a DML operation through third-party applications like a custom application, can this script get the client IP address?</p></li>
  </ol>