Solved

Get list of connected users

Posted on 2013-05-15
3
368 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
  • 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 39

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Live Storage Vmotion VMs with shared VMDK 10 64
insert wont work in SQL 14 22
Weighted Randomizing 6 17
SQL Server group with two values 4 19
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

825 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