[dba-SQLServer] Determining Logged in Users?

Dan Waters dwaters at usinternet.com
Fri Jul 10 11:36:56 CDT 2009


Thanks!  Sounds perfect. 

Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Friday, July 10, 2009 11:24 AM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Determining Logged in Users?

There is a system stored procedure sp_who which returns lots of details
about current users 
and processes.

Stripping the guts out of that procedure and tweaking it a bit I came up
with this:

select distinct 
       loginame=rtrim(loginame),
	   hostname,
	   dbname = db_name(dbid)
from  master.dbo.sysprocesses
where spid >= 0 and spid <= 32767 and dbid <> 0
 
You can refine it a bit further by limiting to it the name of the database
you are interested in.



On 10 Jul 2009 at 11:01, Dan Waters wrote:

> Thanks Stuart -
> 
> I forgot to say that I need to do this programmatically.  This system has
> user licenses, and I need to be able to prevent more people from logging
in
> than my customer has purchased licenses for.
> 
> Dan
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart
> McLachlan
> Sent: Friday, July 10, 2009 10:39 AM
> To: Discussion concerning MS SQL Server
> Subject: Re: [dba-SQLServer] Determining Logged in Users?
> 
> In the Management Studio, under Management - Activity Logs - Process Info,
> you can see a 
> list of all current Users and Processes.
> 
> On 10 Jul 2009 at 9:47, Dan Waters wrote:
> 
> > 
> > If I have multiple Access FE's / one SQL BE, and I'm not using Access's
> > username/password method, what can I do to see who is logged in to
> database?
> > 
> > 
> > I'll be using ODBC table links to the BE.  Does SQL keep track of users
> who
> > are connected?  How could I reach that list?
> > 
> > Thanks!
> > Dan
> > 
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> > 
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list