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 +

[elsuket]

Bonjour,

Avec le Profiler, lorsqu'on regarde la ligne "Audit Login", et qu'on affiche toute les colonnes, on peut afficher le nom de la base de données, et le nom de Login sous lequel la navigation dans la base de données est effectuée en cochant la case "Database Name" ...

En se basant là-dessus, vous pouvez faire une trace côté serveur comme je le montre dans cet tutoriel.

@++

[mikedavem]

En testant ce matin les alertes SQL Server basées sur WMI (et les notifications d'événements) fonctionnent ... mais ... on ne récupère que la base de données par défaut de la connexion initiée mais c'est tout. On retrouve la même chose avec le profiler me semble t'il ??

Autrement dit si toutes les connexions ont par défaut la base de données master... vous ne verrez rien ... De plus en admettant que la base de données par défaut soit la bonne, on ne pourra cependant voir les interrogations interbases et les changements de contexte comme le précise SQLPro ..

++

[kagemaru]

Il ne te reste plus qu'à filtrer sur la colonne TextData..... ou bien modifier la configuration du login pour lui mettre la base par défaut ou bien encore de modifier la chaîne de connexion dans le programme.

mes 2 eurocents.

[Invité]

En testant ce matin les alertes SQL Server basées sur WMI (et les notifications d'événements) fonctionnent ... mais ... on ne récupère que la base de données par défaut de la connexion initiée mais c'est tout. On retrouve la même chose avec le profiler me semble t'il ??

C'est ce que mes tests prouvent aussi !

[Invité]

Il ne te reste plus qu'à filtrer sur la colonne TextData.....

Je ne comprends pas ?

ou bien modifier la configuration du login pour lui mettre la base par défaut

Pas possible si un user a des droits sur plus d'une base ou s'il est sysadmin... (le but initial de mon projet est de faire du ménage dans les bds et les users aux droits... excessifs...)

ou bien encore de modifier la chaîne de connexion dans le programme.

Je cherche justement à savoir si certaines bds sont utilisées alors tu imagines bien que j'ai encore moins de connaissances sur les potentielles applications qui travailleraient dessus !!


AU final, je pense qu'au lieu de faire dans le raffinement, je vais utiliser la bonne vieille méthode dite de la "Grosse Bertha" :
je mets OFFLINE les bds dont je suppute une non-utilisation et j'attends les plaintes près de mon téléphone...

[kagemaru]

AU final, je pense qu'au lieu de faire dans le raffinement, je vais utiliser la bonne vieille méthode dite de la "Grosse Bertha" :
je mets OFFLINE les bds dont je suppute une non-utilisation et j'attends les plaintes près de mon téléphone...

c'est une méthode très efficace et qui a fait ses preuves !!!

Pour la colonne Textdata du profiler, tu peux filtrer sur ce champ pour toutes les requêtes contenant la chaîne de caractères

Code :

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

mabase.

pour capture les

Code :

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

 SELECT col from mabase.dbo.table

, par ex. Si toutes les requêtes sont faites depuis master, il faut bien qu'ils précisent le nom de la base qq part.

[Invité]

Pour la colonne Textdata du profiler, tu peux filtrer sur ce champ pour toutes les requêtes contenant la chaîne de caractères

Code :

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

mabase.

pour capture les

Code :

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

 SELECT col from mabase.dbo.table

, par ex. Si toutes les requêtes sont faites depuis master, il faut bien qu'ils précisent le nom de la base qq part.

Ok, mais textdata contient le code pour les évènements autre que Logon.

[kagemaru]

oui en effet mais j'imagine que le login va bien lancer une requête à un moment donné

[Invité]

oui en effet mais j'imagine que le login va bien lancer une requête à un moment donné

Ouais ok, mais ça revient à tracer l'activité sur les bds, à la base, je voulais me contenter juste des connections...
En fait, si je veux faire un audit pas trop invasif, il ne me reste plus qu'à cibler les bases de données et à y tracer l'activité dessus...

[Invité]

Pour votre information, en "jouant" avec le trigger de bd LOGON, j'ai planté ma bd SQL Server 2005 SP3 9.0.4285 (de test... ouf !)
Suite à un restore de bd (en plus qui n'avait pas de rapport direct avec mes tests), elle ne me permettait plus de me logguer !
Pourtant, je ne faisais que récupérer des infos que je copiais dans une table...
Du coup, j'ai fait une réinstallation et c'est reparti !

Comme quoi, les tests c'est pas toujours inutiles...

[Invité]

J'ai compris pourquoi j'ai totalement planté mon instance : je me suis trompé de BD en faisant un restore : j'ai écrasé la bd où se trouvait la table où j'écrivais les informations lors du logon... Du coup, il n'était plus possible d'exécuter le le trigger LOGON complètement et l'on ne pouvait plus accéder à rien !
C'est très con de ma part !