Discussion :

[Invité]

Hello.
J'ai plein de bases de données sur différentes instances et je souhaiterais vérifier les connexions qui sont faites sur des bases de données précises dont je doute de l'utilité.
J'ai regardé diverses solutions et j'aimerais bien faire ça par un trigger.
J'ai fait un trigger qui capte le logon mais mon problème est que je n'arrive pas à trouver la base de données sur laquelle le user va se connecter.

Voici mon code (en test...) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE [test].[dbo].[ddl_log](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[ts] [datetime] NOT NULL CONSTRAINT [DF_ddl_log_ts]  DEFAULT (getdate()),
	SPID int,
	ServerName nvarchar(256),
	LoginName nvarchar(256),
	LoginType nvarchar(256),
	SID nvarchar(256),
	ClientHost nvarchar(256),
	IsPooled bit,
	[xx] [xml] NULL,
	DatabaseName nvarchar(256),
 CONSTRAINT [PK_ddl_log] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TRIGGER logon
	ON all server 
		FOR logon
AS
	DECLARE @data XML
	SET @data = EVENTDATA()
	INSERT [test].[dbo].[ddl_log]
	   ( SPID , ServerName , LoginName , LoginType , SID , ClientHost , IsPooled 
		, xx , DatabaseName
		) 
	   VALUES 
		   (
			@data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(256)'), 
			@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(256)'), 
			@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)'), 
			@data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(256)'), 
			@data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(256)'), 
			@data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(256)'), 
			@data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(256)')
			, @data ,
			db_name()
			) ;
GO

J'ai toujours master comme DB_name... Ce qui n'est pas illogique puisque le trigger doit se déclencher sur master, j'imagine
Mais comment récupérer alors la bd que va utiliser ce user ?

Je suis ouvert à toutes propositions.

[SQLpro]

Une connexion (LOGIN) se connecte à un serveur. A ce stade il n'y a donc aucune relation avec une quelconque base, c'est pourquoi cela vous renvoie toujours master car les login y sont stockés dedans.
Ce sont les utilisateurs et non les connexions (LOGIN) qui sont des objets de la base.

Pour faire ce que vous voulez faire, il faut utiliser une procédure planifiée qui va scruter régulièrement les connexions et les stocker dans une table.

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
CREATE TABLE master.dbo.T_AUDIT_CONNEXIONS
(connection_id    UNIQUEIDENTIFIER UNIQUE,
 database_id      INT);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
INSERT INTO master.dbo.T_AUDIT_CONNEXIONS
SELECT connection_id, database_id 
FROM   sys.dm_exec_requests AS R
WHERE  connection_id IS NOT NULL

A +

[Invité]

Pour faire ce que vous voulez faire, il faut utiliser une procédure planifiée qui va scruter régulièrement les connexions et les stocker dans une table.

J'aime pas trop l'idée. Mon instance est déjà bien occupée...
Mettons que j'ai une petit jobine de 10s qui se lance une fois par semaine sur une bd, elle peut facilement passer à travers...

Je vais donc être obligé de passer par le profiler pour faire ça proprement...

[mikedavem]

Les triggers LOGON se déclenchent effectivement après la phase d'authentification au serveur mais avant l'établissement de la session de l'utilisateur...

Vous ne pourrez donc pas obtenir l'information de la base de données ...
J'ai essayé de voir avec les notifications d'événements mais il n'existe aucune classe d'événement disponible pour réaliser cette tâche ...

++

[Invité]

C'est dingue ça, effectivement, même avec Profiler, je n'arrive pas à récupérer la bd, c'est toujours master qui sort !
Et dire qu'une demande aussi basique n'est pas possible simplement...
Il est temps de rentrer...

[SQLpro]

C'est parfaitement logique car à la connexion rien ne dit que je vais faire une requête dans la base X, même si l'on a définit que l'utilisateur devait se connecter à la base Y, car le changement de contexte de base est dynamique et que vous pouvez faire des requêtes interbases.

A +