Get the IP address of a user in calling a stored procedure (SQL)

Question

Is it possible, and if so how, to get a user's remote IP address executing the query, analogously as we can get user's name with: SUSER_SNAME()?

Update before bounty

I am looking for a solution which allows to grab the IP address of an ordinary mortal user, not a database owner. The ideas proposed by TheGameiswar or njc do not allow to capture a user's IP address who has been granted just a execute permission. However, they are excellent ideas to start with the problem. Here I list the essence of the ideas:

Please see the sequence I follow:

create procedure MyStoredProcedure as
select client_net_address 
from sys.dm_exec_connections
where session_id = @@SPID

Now add a user and grant permission:

CREATE LOGIN [user_mortal_jack] WITH PASSWORD=N'LongYouLive!!!';

GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];

When I run the procedure with a query:

EXECUTE AS USER = 'user_mortal_jack'
exec MyStoredProcedure
REVERT

I get error message:

The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

I will get this message even if I grant an additional permission:

grant VIEW SERVER STATE to [user_mortal_jack];

If I change the beginning of the stored procedure to:

create procedure MyStoredProcedure
with execute as OWNER as 

I end up with different sort of error:

Could not obtain information about Windows NT group/user 'blahblah\admin_user', error code 0x534.

Update after bounty

Bounty is granted to Hadi for this single line of code hidden in their answer:

CONNECTIONPROPERTY('client_net_address')

which let's capture the IP address of any mortal user without neither granting any additional rights to the user nor setting the database TRUSTWORTHY ON option nor even creating a procedure WITH EXECUTE AS OWNER clause.

Answer 1

General Info

There are two ways to get the current connection information

1. Getting information from Dynamic Management Views

   SELECT
   conn.session_ID as SPID,
   conn.client_net_address as IPAddress,
   sess.host_name as MachineName,
   sess.program_name as ApplicationName,
   login_name as LoginName
   FROM sys.dm_exec_connections conn
   inner join sys.dm_exec_sessions sess
   on conn.session_ID=sess.session_ID
   

2. Using CONNECTIONPROPERTY function (SQL Server 2008 and newer version):

   select
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address
   

Suggested Solutions

1. If you are looking to grant user for a specific IP address

   CREATE PROCEDURE MyStoredProcedure AS
   BEGIN
       DECLARE @IP_Address varchar(255);
   
       SELECT @IP_Address = CAST(CONNECTIONPROPERTY('client_net_address') as varchar(200))
   
       IF @IP_Address = 'XXX.XXX.XXX.XXX'
       SELECT TOP 1 FROM tb
   
   END
   

2. Assuming that you have a table that contains the granted IP address (i.e. TBL_IP)

   CREATE PROCEDURE MyStoredProcedure AS BEGIN DECLARE @IP_Address varchar(255);

   SELECT @IP_Address = CAST(CONNECTIONPROPERTY('client_net_address') as varchar(200))
   
   IF EXISTS (SELECT 1 FROM TBL_IP WHERE [IP] = @IP_Address )
   SELECT TOP 1 FROM tb
   

   END

3. If you are looking to grant a user (database user) to execute a stored procedure, you should use this command

   GRANT EXECUTE ON MyStoredProcedure TO User;

   There are many detailed article and answers talking about the issue you are facing, and many suggested solutions, such as Setting the Database in TRUSTWORTHY mode (before using it read the first Link below) and Trusting the Authenticator, and other methods. You can find them in the links below

   • SQL Server EXECUTE AS trouble
   • SQL Server execute procedure as user
   • Extending Database Impersonation by Using EXECUTE AS (Extending the Scope of Impersonation section)
   • EXECUTE AS (Transact-SQL)
   • Execute a stored procedure as another user premission

   Note: You can check @SteveFord answer for using TRUSTWORTHY property

4. If you are looking to block connections except specific IP addresses then you should follow this answer

   • Restrict SQL Server connection to specific IP address

   Also there are many scripts that can be used to get client or server IP addresses that can be found in the question below:

   • How to get a client IP address from an SQL Server database

References

• MS SQL 2008: Client IP address on shared hosting
• Finding client IP-Address
• How to get a client IP address from an SQL Server database
• Get Client IP Address in SQL Server