Finding Users Logged Into A Database

[txdave35]

Hey guys,

I am documenting a procedure for restoring our SQL database, and I want to include some tips for finding users logged into the database since SQL will not let you restore a database until everyone logs out.

I have used "exec sp_who", but it lists too much information. Is there way to tell sp_who to only list for a specific dbname?

Any other methods for finding users? Thanks!

 

[hmckillop]

Sp_who is effectively a query on systables, so anything is possible.


Have a look at sp_helptext sp_who it has all the code in sp_who

Have a look at the below code
modify it to do what you need

 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  master.dbo.sysprocesses  
 where spid >= 0 and spid <= 32767 --AND  
      -- upper(cmd) <> 'AWAITING COMMAND'  
		and db_name(dbid)=(@YourDBNAME)

"I'm living so far beyond my income that we may almost be said to be living apart