Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Get list of connected users

Posted on 2013-05-15
3
Medium Priority
?
377 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 46

Accepted Solution

by:
Kent Olsen earned 2000 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 46

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

647 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