General Info

There are two ways to get the current connection information

1. Getting information from Dynamic Management Views

   SELECTconn.session_ID as SPID,conn.client_net_address as IPAddress,sess.host_name as MachineName,sess.program_name as ApplicationName,login_name as LoginNameFROM sys.dm_exec_connections conninner join sys.dm_exec_sessions sesson conn.session_ID=sess.session_ID

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

   selectCONNECTIONPROPERTY('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 ASBEGIN    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 tbEND

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

   CREATE PROCEDURE MyStoredProcedure ASBEGIN 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

You can use connections DMV to accomplish that..

select ec.client_net_address,* from sys.dm_exec_connections ecjoinsys.dm_exec_requests rqon rq.connection_id=ec.connection_idcross applysys.dm_exec_sql_text(rq.sql_handle) txtwhere txt.text like '%your stored proc%'

MSDN for client_net_address

Host address of the client connecting to this server. Is nullable.

Using the EXECUTE AS OWNER statement in a CREATE PROCEDURE Statement:

From MSDN

When a user executes a module that has been specified to run in a context other than CALLER, the user's permission to execute the module is checked, but additional permissions checks on objects that are accessed by the module are performed against the user account specified in the EXECUTE AS clause. The user executing the module is, in effect, impersonating the specified user.

The context specified in the EXECUTE AS clause of the module is valid only for the duration of the module execution. Context reverts to the caller when the module execution is completed.

The following must be created by a user who has permissions to query the DMVs

CREATE PROCEDURE MyStoredProcedureWITH EXECUTE AS OWNERASBEGINSET NOCOUNT ON   SELECT TOP 1   FROM tb   INNER JOIN sys.dm_exec_connections cn         ON tb.client_net_address = cn.client_net_address   WHERE cn.Session_Id = @@SPIDEND

Then you will need to give the users permissions to execute the stored procedure:

Update to create the right permissions

You will need to set your database to Trustworthy (see Set Database to Trustworthy:

ALTER DATABASE MyDataBase SET TRUSTWORTHY ONCREATE LOGIN [user_mortal_jack] WITH PASSWORD=N'LongYouLive!!!';CREATE USER [user_mortal_jack] FOR LOGIN [user_mortal_jack] WITH DEFAULT_SCHEMA=[dbo]GOGRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];

I have tested this and this now works as expected