Abrufen der IP-Adresse eines Benutzers beim Aufrufen einer gespeicherten Prozedur (SQL)

Verwenden der EXECUTE AS OWNER-Anweisung in einer CREATE PROCEDURE-Anweisung:

Von _ ​​ MSDN

Wenn ein Benutzer ein Modul ausführt, das für die Ausführung in einer .__ angegeben wurde. Kontext anders als CALLER, die Berechtigung des Benutzers zur Ausführung des Moduls wird markiert, aber zusätzliche Berechtigungen werden für Objekte geprüft, die .__ sind. Der Zugriff durch das Modul erfolgt für das Benutzerkonto in der EXECUTE AS-Klausel angegeben. Der Benutzer, der das Modul ausführt, ist in der Tat, den angegebenen Benutzer imitieren. 

Der in der EXECUTE AS-Klausel des Moduls angegebene Kontext ist gültig nur für die Dauer der Modulausführung. Der Kontext wird auf .__ zurückgesetzt. Aufrufer, wenn die Modulausführung abgeschlossen ist.

Das Folgende muss von einem Benutzer erstellt werden, der über Berechtigungen zum Abfragen der DMVs verfügt

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

Dann müssen Sie den Benutzern Berechtigungen zum Ausführen der gespeicherten Prozedur erteilen:

Aktualisieren Sie, um die richtigen Berechtigungen zu erstellen

Sie müssen Ihre Datenbank auf "Vertrauenswürdig" setzen (siehe Setzen Sie die Datenbank auf "Vertrauenswürdig" :

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];

Ich habe das getestet und funktioniert jetzt wie erwartet

Sie können Verbindungen DMV verwenden, um das zu erreichen.

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 für client_net_address

Hostadresse des Clients, der eine Verbindung zu diesem Server herstellt. Ist nullfähig.

Um die IP-Adresse und den Benutzernamen eines beliebigen Anrufers abzurufen, ohne ihm alle besonderen Berechtigungen zu erteilen, können Sie den Server und die Datenbank ein wenig betrügen . Um dies zu erreichen, sind ein paar Dinge erforderlich:

1. ALTER DATABASE MyDataBase SET TRUSTWORTHY ON
2. Erstellen Sie in meinem Beispiel ein Login (nicht [user_mortal_jack]) [user_immortan_joe]
3. Erstellen Sie einen Benutzer für [user_immortan_joe] In MyDataBase
4. Im Kontext von mastergrant VIEW SERVER STATE to [user_immortan_joe];
5. Erstellen Sie in MyDataBase eine gespeicherte Prozedur (nicht MyStoredProcedure, in meinem Beispiel get_ip), Die ein int empfängt, das einen bestimmten session_id - Parameter darstellt, wird output (Ausgabe, nicht Rückgabe) die IP-Adresse oder das session_id. Erstellen Sie es with execute as 'user_immortan_joe'.
6. Erstellen Sie MyStoredProcedure so, dass mit der Hilfe von get_ip Und von SUSER_SNAME() die IP-Adresse und der Benutzername des Anrufers zurückgegeben werden.

Auf diese Weise erhalten Sie die IP-Adresse und den Benutzernamen eines Anrufers von MyStoredProcedure, der das Prinzip des geringsten Privilegs respektiert und die Probleme vermeidet, auf die Sie bei der Suche nach einer Lösung gestoßen sind.

Beispielskript:

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