SQL Server - Ligne 100 - Trace utilisateur

Code: Tout sélectionner

--Table permettant de stocker les connexions
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TraceUserSession]') AND type in (N'U'))
DROP TABLE [dbo].[TraceUserSession]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TraceUserSession](
[spid] [int] NULL,
[UserSage100] [nvarchar](max) NULL,
[UserWindows] [nvarchar](max) NULL,
[TypeCnx] [nchar](10) NULL,
[CreatorCnx] [nchar](10) NULL
) ON [PRIMARY]
GO
--Trigger permettant d'alimenter la table "TraceUserSession" avec les nouvelles connexions
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRC_CIAL_USERSESSION]'))
DROP TRIGGER [dbo].[TRC_CIAL_USERSESSION]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TRC_CIAL_USERSESSION]
ON [dbo].[F_PROTECTIONCIAL]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
Declare @marqueur smallint, @userSage100 varchar(max), @userWindows varchar(max)
Declare @typeCnx varchar(max), @creatorCnx varchar(max)

If (Select count(*) From TraceUserSession Where spid=@@spid)=0
Begin
Select @marqueur = cbMarq From Inserted
Select @userSage100 = PROT_USER From F_PROTECTIONCIAL Where CbMarq = @marqueur
Select @userWindows=RTrim(nt_domain) + '\' + RTrim(nt_username) From sys.sysprocesses Where spid=@@spid
Select @typeCnx=CB_Type, @creatorCnx=CB_Creator from cbUserSession Where cbSession=@@spid
Insert Into TraceUserSession(spid, UserSage100, UserWindows, TypeCnx, CreatorCnx)
Values(@@spid,@userSage100,@userWindows,@typeCnx,@creatorCnx)
End
END
GO

Code: Tout sélectionner

--Table permettant de stocker les connexions
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TraceUserSession]') AND type in (N'U'))
DROP TABLE [dbo].[TraceUserSession]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TraceUserSession](
[spid] [int] NULL,
[UserSage100] [nvarchar](max) NULL,
[UserWindows] [nvarchar](max) NULL,
[TypeCnx] [nchar](10) NULL,
[CreatorCnx] [nchar](10) NULL
) ON [PRIMARY]
GO
--Trigger permettant d'alimenter la table "TraceUserSession" avec les nouvelles connexions
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRC_CIAL_USERSESSION]'))
DROP TRIGGER [dbo].[TRC_CIAL_USERSESSION]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TRC_CIAL_USERSESSION]
ON [dbo].[F_PROTECTIONCIAL]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
Declare @marqueur smallint, @userSage100 varchar(max), @userWindows varchar(max)
Declare @typeCnx varchar(max), @creatorCnx varchar(max)

If (Select count(*) From TraceUserSession Where spid=@@spid)=0
Begin
Select @marqueur = cbMarq From Inserted
Select @userSage100 = PROT_USER From F_PROTECTIONCIAL Where CbMarq = @marqueur
Select @userWindows=RTrim(nt_domain) + '\' + RTrim(nt_username) From sys.sysprocesses Where spid=@@spid
Select @typeCnx=CB_Type, @creatorCnx=CB_Creator from cbUserSession Where cbSession=@@spid
Insert Into TraceUserSession(spid, UserSage100, UserWindows, TypeCnx, CreatorCnx)
Values(@@spid,@userSage100,@userWindows,@typeCnx,@creatorCnx)
End
END
GO

OokieDookie a écrit:Bonjour à tous,

Du nouveau sur le forum, bishr nous a enfin trouvé comment récupérer le user Sage d'une session.
Ca valait bien un gros coup de pub et une publication dans la section savoir-faire !

Bonne journée