Solved

Get list of connected users

Posted on 2013-05-15
3
364 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:
Kdo 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:Kdo
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now