find duplicate loginame

Question

find duplicate loginame

Mad-Dog

SSCertifiable

Points: 6117
More actions
November 24, 2015 at 7:02 am

#322545

Hi,
how i can find duplicate loginame in sys.sysprocesses where i can have multiple loginame from one host but not the same loginame from second host?
THX

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

You shouldn't be using sys.sysprocesses anymore. You should be using the new DMO's sys.dm_exec_sessions and/or sys.dm_exec_connections depending on what you are looking at. I'm not sure what you are actually needing to accomplish with this. A query to find out if someone has an existing connection from another host would be something like this:

SELECT

*

FROM

sys.dm_exec_sessions AS DES

WHERE

DES.host_name <> HOST_NAME() AND

DES.login_name = SYSTEM_USER;

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 2

ok..

this is not what i'm looking for.

i need to see if a user is connected from 2 different hostname.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Something like this?

SELECT login_name

FROM sys.dm_exec_sessions AS DES

GROUP BY DES.login_name

HAVING COUNT( DISTINCT DES.host_name ) > 1

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jack Corbett SSC Guru Points: 184393 More actions · Answer 4

Mad-Dog (11/24/2015)
ok..
this is not what i'm looking for.
i need to see if a user is connected from 2 different hostname.

So

SELECT

DES.login_name,

COUNT(DISTINCT DES.host_name) as login_hosts

FROM

sys.dm_exec_sessions AS DES

GROUP BY

DES.login_name

ORDER BY

login_hosts DESC

OR

DECLARE @login_name SYSNAME = {user name}

SELECT

DES.login_name,

COUNT(DISTINCT DES.host_name) as login_hosts

FROM

sys.dm_exec_sessions AS DES

WHERE

DES.login_name = @login_name

GROUP BY

DES.login_name

ORDER BY

login_hosts DESC

or what Luis posted while I was typing my reply.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question