Ответ 1

Общая информация

Есть два способа получить текущую информацию о подключении

1. Получение информации из Динамические представления Managemenet

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. Использование CONNECTIONPROPERTY функция (SQL-Server 2008 и более новая версия):

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. Если вы хотите предоставить пользователю определенный IP-адрес

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. предполагая, что у вас есть таблица, содержащая предоставленный IP-адрес (т.е. 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. если вы хотите предоставить пользователю (пользователю базы данных) выполнение хранимой процедуры, вы должны использовать эту команду

GRANT EXECUTE ON MyStoredProcedure TO User;

Существует много подробных статей и ответов о проблеме, с которой вы столкнулись, и многие предлагаемые решения, такие как установка базы данных в режиме TRUSTWORTHY (перед ее использованием прочитайте первую ссылку ниже) и доверяйте аутентификатору и другим методы. Вы можете найти их в ссылках ниже

• SQL Server EXECUTE AS проблема
• SQL Server выполняет процедуру как пользователь
• Расширение олицетворения базы данных с помощью EXECUTE AS (Расширение области олицетворения)
• EXECUTE AS (Transact-SQL)
• выполнить хранимую процедуру в качестве другого пользовательского запроса

Примечание. Вы можете проверить ответ @SteveFord для использования свойства TRUSTWORTHY

4.Если вы хотите заблокировать подключения, за исключением определенных IP-адресов, вы должны следовать этому ответу

• Ограничить подключение SQL Server к определенному IP-адресу

Также существует множество сценариев, которые можно использовать для получения IP-адресов клиента или сервера, которые можно найти в следующем вопросе:

• Как получить IP-адрес клиента из базы данных SQL Server

Ссылки

• MS SQL 2008: IP-адрес клиента на общем хостинге
• Поиск IP-адреса клиента
• Как получить IP-адрес клиента из базы данных SQL Server
• Получить IP-адрес клиента в SQL Server

Ответ 2

Вы можете использовать соединения DMV для выполнения этого.

select ec.client_net_address,* from sys.dm_exec_connections ec
join
sys.dm_exec_requests rq
on rq.connection_id=ec.connection_id
cross apply
sys.dm_exec_sql_text(rq.sql_handle) txt
where txt.text like '%your stored proc%'

MSDN для client_net_address

Адрес хоста клиента, подключающегося к этому серверу. Недействителен.

Ответ 3

Использование оператора EXECUTE AS OWNER в CREATE PROCEDURE Заявление:

От MSDN

Когда пользователь выполняет модуль, который был указан для запуска в контекст, отличный от CALLER, разрешение пользователя на выполнение модуля проверяется, но дополнительные проверки прав на объекты, которые доступ к модулю выполняется против учетной записи пользователя указанных в предложении EXECUTE AS. Пользователь, выполняющий модуль, в действительности, олицетворяя указанного пользователя.

Контекст, указанный в предложении EXECUTE AS модуля, действителен только на время выполнения модуля. Контекст возвращается к вызывающий, когда выполнение модуля завершено.

Следующее должно быть создано пользователем, у которого есть разрешения на запрос DMV

CREATE PROCEDURE MyStoredProcedure
WITH EXECUTE AS OWNER
AS
BEGIN
SET 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 = @@SPID
END

Затем вам необходимо предоставить разрешениям пользователей для выполнения хранимой процедуры:

Обновление для создания правильных разрешений

Вам нужно будет настроить свою базу данных на Надежность (см. Установить базу данных на надежную:

ALTER DATABASE MyDataBase SET TRUSTWORTHY ON

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

CREATE USER [user_mortal_jack] FOR LOGIN [user_mortal_jack] WITH DEFAULT_SCHEMA=[dbo]
GO

GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];

Я тестировал это, и теперь он работает как ожидалось

Ответ 4

Чтобы получить IP-адрес вызывающего абонента и имя пользователя без предоставления всех специальных разрешений, вы можете немного обмануть сервер и базу данных. Для этого необходимо несколько вещей:

• ALTER DATABASE MyDataBase SET TRUSTWORTHY ON
• Создайте логин (не [user_mortal_jack]) в моем примере [user_immortan_joe]
• Создайте пользователя для [user_immortan_joe] в MyDataBase
• В контексте master grant VIEW SERVER STATE to [user_immortan_joe];
• В MyDataBase создайте хранимую процедуру (не MyStoredProcedure, в моем примере get_ip), которая получает int, представляющий конкретный параметр session_id, будет output (выводить, а не возвращать) IP-адрес от этого session_id. Создайте его with execute as 'user_immortan_joe'
• Создайте MyStoredProcedure таким образом, чтобы с помощью get_ip и SUSER_SNAME() возвращался IP-адрес и имя пользователя вызывающего.

Таким образом вы получаете IP и имя пользователя любого вызывающего абонента MyStoredProcedure в соответствии с принципом наименьшей привилегии и избегаете проблем, с которыми вы столкнулись при решении.

Пример script:

use MyDataBase
go
alter database MyDataBase set  trustworthy on;
go

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

CREATE LOGIN [user_immortan_joe] WITH PASSWORD=N'ToTheGatesOfValhalla!!!';
go
create user [user_immortan_joe];
go

use master 
go
grant VIEW SERVER STATE to [user_immortan_joe];

use MyDataBase
go


create  PROCEDURE get_ip 
@spid int, @ip varchar(50) output 
with execute as 'user_immortan_joe'
as
begin

 select @ip = client_net_address 
 from sys.dm_exec_connections
 where session_id =  @spid
end;
go

create procedure MyStoredProcedure
as
begin
 declare @spid int = @@spid, @ip varchar(50);
 exec dbo.get_ip @spid,@ip output;
 select @ip as ipAddress ,SUSER_SNAME() as userName
end
go

GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];
go

EXECUTE AS USER = 'user_mortal_jack'
exec MyStoredProcedure
REVERT