Discussion :

[eamrf]

J'ai besoin de faire une vue à la demande pour visualiser les clients connectés à mes bases de données.
J'ai des authentifications SQL mais je ne voudrais pas utiliser des connexions avec roles processadmin ou sysadmin.

J'aimerai une connexion avec juste assez de droit pour consulter la liste sp_who ou faire une vue a partir de
select spid, hostname, nt_username Utilisateur , cmd, program_name Programme from master..sysprocesses
where dbid = db_id(@db) .

J'ai essayé avec db_data_reader sur master mais ça ne marche, il semblerait que seul le role serveur processadmin permette de visualiser les connexions ... mais aussi d'arreter les process, et ça je ne veux pas...

Je suis avec des serveurs SQL2005, SQL 2008 et 2012.
Une solution avec une prise de risque limitée existe-t-elle?
Merci d'avance

[SQLpro]

select spid, hostname, nt_username Utilisateur , cmd, program_name Programme from master..sysprocesses where dbid = db_id(@db) .

Cette table est obsolète depuis la version 2005 de SQL Server et vous donnera des informations fausses !
Vous devez travailler avec les vues systèmes sys.dm_exec_requests, sys.dm_exec_connections et sys.dm_exec_sessions et donner le privilège VIEW SERVER STATE aux connexions désirées.

A +

[eamrf]

Merci, en effet avec le privilege VIEW SERVER STATE, la vue systeme sys.dm_exec_sessions est la plus parlante dans mon cas.

[eamrf]

petit ajout subisdaire ?
Comment relier la vue sur les sessions actives avec une seule des bases (database_id) car sur le serveur SQL il y a plusieurs bases de données? sur SQL2012 il semble y avoir l'info mais pas dans SQL2005...

[elsuket]

Bonjour,

C'est exact. Vous pouvez vous en remettre à la DMV sys.dm_exec_requests et écrire :

Code :

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

1
2
3
4
SELECT		[...]
FROM		sys.dm_exec_sessions AS S
INNER JOIN	sys.dm_exec_requests AS R
			ON R.session_id = S.session_id

@++

[eamrf]

Bonjour,

C'est exact. Vous pouvez vous en remettre à la DMV sys.dm_exec_requests et écrire :

Code :

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

1
2
3
4
SELECT		[...]
FROM		sys.dm_exec_sessions AS S
INNER JOIN	sys.dm_exec_requests AS R
			ON R.session_id = S.session_id

@++

Merci mais on dirait qu'on ne voit que les connexions qui passent au moment t des requetes...

Y a-t-il un autre moyen de lié la session_id à la database_id ?

[elsuket]

Merci mais on dirait qu'on ne voit que les connexions qui passent au moment t des requetes...

C'est exact. Une session peut exécuter une requête au plus.
Si elle n'en exécute pas, sous SQL Server 2005, on ne peut pas connaître le contexte de base de données dans lequel s'est exécuté la dernière requête.

Si je comprend bien, vous souhaitez auditer le nombre de connexions par base de données.
Avez-vous en fait besoin de le connaître en "live" ? Peut-être écrivez-vous une requête pour stocker dans une table le résultat de celle-ci à l'aide d'un job de l'Agent SQL Server ?
S'il s'agit seulement de mesurer le nombre de connexions par base de données au cours d'une journée, c'est possible par un groupement à l'aide d'une trace SQL Profiler.

Une dernière question : pourquoi ne pas mettre à jour l'instance vers SQL Server 2008 ou suivants ?
Ces versions de SQL Server permettent d'auditer cela à l'aide de la fonctionnalité événements étendus.

@++

[eamrf]

j'ai des applications clientes qui sont connectées simultanément sur deux serveurs SQL.

On doit effectuer des reboot (patch MS) et normalement les clients se reconnectent à la base perdue mais ... pas toujours.
Donc l'idée etait de faire une vue en "live" sur chaque serveur pour voir les applis connectées, normalement on devrait avoir les mêmes connexions (host_name) sur les deux serveurs.

Le passage en SQL2008 a déja été faite sur une partie du parc.. mais il reste du SQL2005...

A quoi pensez vous quand vous parlez de "la fonctionnalité événements étendus" sur du SQL2008 et superieurs?

[elsuket]

Donc l'idée etait de faire une vue en "live" sur chaque serveur pour voir les applis connectées, normalement on devrait avoir les mêmes connexions (host_name) sur les deux serveurs.

D'accord. Donc sans ce cas la jointure avec sys.dm_exec_requests vous donnera en "live" les requêtes en cours d'exécution.
A court terme, vous pouvez aussi modifier l'application pour qu'elle écrive dans une petite table le nom d'utilisateur avec la date de dernière connexion / ouverture de session.

A quoi pensez vous quand vous parlez de "la fonctionnalité événements étendus" sur du SQL2008 et superieurs?

C'est une fonctionnalité plus puissante et plus performante que SQL Profiler.
Pour couvrir votre cas, on pourrait avoir une session d'événements étendus avec une cible histogramme qui compte le nombre de requêtes qui sont passés sur une base.
Il suffit alors d'interroger l'histogramme pour vérifier que des requêtes se sont bien exécutées sur telle ou telle base.
Pour en savoir plus, vous pouvez vous en remettre à cet article, et/ou au livre qui est référencé dans ma signature (Chapitre 19 je crois).

@++

[elsuket]

Il y a peut-être un peu plus simple, si vous avez un login par client.
Dans ce cas, si on active l'audit des connexions qui se sont faites avec succès en plus de celles qui ont terminé en échec, il suffit de lire le journal d'événements de SQL Server pour savoir si des connexions se sont faites sous tel ou tel login.
Il suffit de lire le log courant (EXEC xp_readerrorlog 0, 1), qui est créé à chaque démarrage d'instance.

@++