Is it possible, and if so how, to get user's remote ip address executing the query, analogously as we can get user's name with:
create procedure MyStoredProcedure as
where session_id = @@SPID
CREATE LOGIN [user_mortal_jack] WITH PASSWORD=N'LongYouLive!!!';
GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];
EXECUTE AS USER = 'user_mortal_jack'
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.
grant VIEW SERVER STATE to [user_mortal_jack];
create procedure MyStoredProcedure
with execute as OWNER as
Could not obtain information about Windows NT group/user 'blahblah\admin_user', error code 0x534.
There are 2 ways to get the current connection informations
1. Getting Informations from Dynamic Managemenet 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
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 granted ip address (i.e.
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
Note: You can check @SteveFord answer for using
4.If you are looking to block connections except specific IP addresses then you should follow this answer
Also there are many scripts that can be used to get client or Server IP addresses that can be found in the Question below: