List of current users?

[Kalor]

Hi all,

I just need to know which system table shows all users who are currently logged in.
Sounds like such a basic requirement but I haven't found it yet. I've tried sysprocesses, but it only has records for the users that are currently running something. IS there a table for idle users?

 

[bluehammer]

the stored proc sp_who or sp_who2 give you that information - run it against the master database.

It pulls information from the sysprocesses table, and if you take a look at the code for the stored proc you'll see exactly how they do that.

cheers
AB

 

[Kalor]

Thanks, but I already tried that.

The situation is:
I have a SQL Server database, and over 100 users of a third party shelf app (MRI) that use it.
SOME users access it remotely via Citrix.
I need to know the ones that don't.

In Citrix I can call up a list of users that are running the mri.exe. *Most* of these also appear in a sp_who, or my similar sql:
select substring(hostname, 1,charindex(' ', hostname))
from master.dbo.sysprocesses
where substring(hostname, 1,charindex(' ', hostname))
<> ''

The ones that don't appear seem to be idle, so either:
1) the third party app logs into the SQL Server database every time the user does something (which sounds unlikely)
2) there's a magic table I don't know about which stores users who *login* but not *process*.

Any ideas? I hope that's clear...

 

[tlbroadbent]

The sysprocesses table, sp_who or sp_who2 should show all current connections.

It is actually quite likely that an application opens a connection, does some processing, returns data to the user screen and then closes the connection. It will then open the connection when the user submits an update or new query. This technique was commonly used in SQL 7 and earlier versions to free connections when using Per Server licensing. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[fluteplr]

Another possibility is that it is using a 3 tiered or multi tiered approach where there is a server side dll that uses a single connection to service a number of users.

 

[euman]

I asked something like this a while back (title was something like &quot;Does MSDE forget who is logged-in/connected?&quot.

Are you saying that the answer is &quot;you can't really be sure - (for a variety of reasons)&quot;?

 

[Guest_imported]

There is any thing Like Magic Tables in Sql- Server? if so what does this table store about. Any info ragrding the Database and table Please help me.
Kumar_nav2000@yahoo.com

 

[tlbroadbent]

Magic tables,

Please post your query in a new thread and provide more information about Magic Tables? What are or is &quot;Magic Tables?&quot; Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.