Identifier des sessions de connexion SQL Server

**StripMat** · 26/05/2014, 16h38

Bonjour,

Je cherche à identifier des sessions de connexion à un même compte utilisateur simultanément, mais malheureusement je ne parviens pas à trouver, en SQL, une variable à récupérer permettant d'identifier chacune de ces sessions (peut-être dans les tables systèmes ?), j'ai bien essayé SESSION_USER mais je récupers le nom d'utilisateur... :/

Existe-il un moyens d'identifier chaque session ? L'objectif étant de réaliser des triggers remplissant des tables de logs, mais le problème c'est que les utilisateurs de mon appli se connectent tous avec le même utilisateur SQL.

En espérant qu'une telle solution existe

**aieeeuuuuu** · 26/05/2014, 16h57

Bonjour,

dm_exec_sessions sera peut être un bon début pour vous... mais je n'ai pas bien compris ce que vous vouliez obtenir comme informations

**StripMat** · 26/05/2014, 17h48

Merci pour la table, effectivement cela peut correspondre à mon besoin.

Je cherche à créer des fichiers de logs, pour cela je dois récupérer les informations propres à chaque sessions (faire le lien entre IDSession et un utilisateur enregistré dans une table de notre base) pour ensuite écrire tout sa dans une table log ou un fichier text, peu importe.

Comme chaque utilisateur de notre application vient taper dans notre BDD avec le même Utilisateur, il faut définir quel utilisateur physique vient se connecter à notre base de donnée, je souhaite faire cela par le biais des sessions et leur ID.

Je précise que je débute en SQL et sur SQL Server, si je m'exprime mal pardonnez le moi

PS : En testant un peu des requêtes sur la table dm.exec_session, j'ai remarqué que lorsque je me connecte avec notre compte utilisateur par la console TSE, il persiste dans la table même une fois déconnecté, mais dès que je le reconnecte, l'heure de login de ce dernier change mais son ID ne change jamais, est-ce normal ? Et pourquoi reste-il toujours dans la table ?

**dbaffaleuf** · 26/05/2014, 18h45

session_id n'est pas non plus une bonne façon d'identifier un utilisateur physique. Si un utilisateur A se connecte il obtient un session_id=54 par exemple, il se déconnecte et un autre utilisateur B arrive et obtiendra le même session_id=54.

Si la machine de connexion est aussi centralisée (serveur d'applications, 3 tiers+) et que le login de connexion est le même, alors je ne vois pas comment identifier l'utilisateur physique derrière la connexion. Il faut le faire en amont de SQL Server, au niveau du frontal applicatif.

Si on est en client serveur pur (client lourd -> SQL Server), alors la machine ou l'IP (host_name dans sys.dm_exec_sessions, client_net_address dans sys.dm_exec_connections) peut permettre d'identifier l'origine physique de la connexion.

**StripMat** · 27/05/2014, 09h20

Merci pour vos réponses, effectivement j'ai eu beau me creuser la cervelle, je pense que nous allons gérer ces logs au niveau applicatif.

**StripMat** · 27/05/2014, 10h52

Nouvelle question, j'ai tenté de créer un deuxième utilisateur avec le même nom de connexion mais j'ai reçu l'erreur 15063. J'ai fais des recherches sur cette erreur, sans succès :/

Est-ce possible qu'a l'identification d'un nouveau client un script puisse lui créer un utilisateur SQL avec un nom de connexion commun à tous les autres Utilisateurs ?

**SQLpro** · 28/05/2014, 10h48

Un même nom de connexion peut être partagé par autant d'utilisateur (personne physique) que l'on souhaite.

Une connexion et les utilisateurs SQL ne sont que des "profils" de navigation. Ce n'est d'ailleurs pas l'utilisateur personne physique qui se connecte, mais l'application.

Une solution consiste à créer autant d'utilisateurs SQL et de compte de connexion qu'il y a d'utilisateurs physique. Le nombre d'utilisateurs/compte de connexion créé n'a pas de réelle importance si vous mutualisez les privilèges via les rôles et les schémas SQL.

A +

**StripMat** · 28/05/2014, 14h41

Je vous remercie pour vos réponses, nous voulions éviter d'avoir à créer autant d'utilisateur/connexions SQL que d'utilisateurs physiques mais je crois qu'on aura pas trop le choix

Merci pour votre aide !

**mikedavem** · 28/05/2014, 16h53

J'ai eu à travailler avec une équipe de développement sur la même problématique pour un client qui avait des contraintes de règlementation CFR PART 11.
Nous devions pouvoir retrouver l'utilisateur qui était à l'origine d'une modification de données. L'application utilisait une architecture 3 tiers avec une connexion à la base de données effectuée par unique compte applicatif. Il a été décidé de modifier l'application pour que celle-ci propage le compte utilisateur utilisé depuis le client vers la base de données. Une autre voix architecturale ...

++

**StripMat** · 30/05/2014, 14h16

Merci pour votre témoignage, nous avons décidé de finalement créer une connexion et un utilisateur SQL par utilisateur physique enregistré.

Sa n'a plus aucun rapport avec le titre de ce thread mais je suis face à un problème assez pénible a résoudre. J'éssai de scripter la création d'une base de donnée dynamiquement, avec des variables passées en paramètres. Malheureusement, sa semble être impossible d'executer une fonction CREATE TABLE $(MaVariable), cela me retourne une erreur. Une solution consisterai à déclarer une fonction (String) contenant les instructions SQL et les variables et ensuite l'éxecuter.

Voici mon code :

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
USE [master]
GO
DECLARE @sql varchar(500)
set @sql = 'CREATE DATABASE ['+$(NewDTB)+'] /***** $(newDTB) => Variable passée en paramètre *****/
	ON PRIMARY
	(NAME = N'+$(NewDTB)+',
		FILENAME = N c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\ '+ $(NewDTB) +'.mdf'',
		SIZE = 5000, FILEGROWTH = 10%)
	LOG ON 
	(NAME = N'+$(NewDTB)+'_log,
			FILENAME = N c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\ '+ $(NewDTB) +'.ldf'',
			SIZE = 5000,
			FILEGROWTH = 10%)' 
 
 
EXEC(@sql)

La problématique est simple : Faire fonctionner ce script, j'éssai de l’exécuter en ligne de commande avec un paramètre pour le nom de la nouvelle base, je n'ai pas réussi, la syntaxe me retourne toujours des erreurs. J'ai vraiment un problème avec les quotes... Si quelqu'un saurait me le "corriger", j'apprécierai ! De même, s'il existe une meilleure méthode, n'hésitez pas !