Solved

Get list of connected users

Posted on 2013-05-15
3
373 Views
Last Modified: 2013-05-15
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!!
0
Comment
Question by:toddinho
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 39169063
execute sp_who;



That'll get you a good start.


Good Luck,
Kent
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39169247
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39169607
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question