Get list of connected users

Hello,

I need to get a list of active users in the database, with an ID for each of them... In mySQL i used the column ID from INFORMATION_SCHEMA.PROCESSLIST... Is there any equivalent for sql server?

I tried sys.sysprocesses... I suppose the user ID would be "sid", but it's a binary column... Is that right? if it's a GUID, shouldn't it be a uniqueidentifier?

Thanks for the help!!
toddinhoAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
execute sp_who;



That'll get you a good start.


Good Luck,
Kent
0
 
lcohanDatabase AnalystCommented:
Check this out:

http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

Or my own version of sp_who...

usage:


exec master..sp_who3 @dbname ='YourDBname', @login = 'LoginName';
exec master..sp_who3 @dbname ='YourDBname', @login = null;
exec master..sp_who3 @dbname =null, @login = 'LoginName';



USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_who3]    Script Date: 05/15/2013 14:53:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_who3] (@dbname sysname, @login sysname)
as

--Who/What Is Running
create table #Who (
      SPID sysname null,
      Status sysname null,
      Login sysname null,
      HostName sysname null,
      BlkBy sysname null,
      DBName sysname null,
      Command sysname null,
      CPUTime sysname null,
      DiskIO sysname null,
      LastBatch sysname null,
      ProgramName sysname null,
      SPID1 sysname null,
      requestid sysname null
)
insert into #who exec sp_who2

if @dbname is not null and @login is not null
select * from #who       
where
            dbname = @dbname
             and login=@login

if @dbname is not null and @login is null
select * from #who       
where
            dbname = @dbname
--             and login=@login

if @dbname is null and @login is not null
select * from #who       
where
--            dbname = @dbname and
            login=@login

drop table #who
0
 
Kent OlsenData Warehouse Architect / DBACommented:
I'm usually opposed to calling an SP to load data into a temp table, then querying the temp table for the results.  But in this case, it makes a lot of sense.

The OP may also be able to use the core query in sp_who:

      select spid , ecid, status
              ,loginame=rtrim(loginame)
            ,hostname ,blk=convert(char(5),blocked)
            ,dbname = case
                                    when dbid = 0 then null
                                    when dbid <> 0 then db_name(dbid)
                              end
              ,cmd
              ,request_id
      from  sys.sysprocesses_ex
      where spid BETWEEN 1 AND 24766 AND
            upper(cmd) <> 'AWAITING COMMAND'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.