[AccessD] Users in SQL Server

Sp_who and sp_who2 will give you processes and connections...not unique
users.

This gives you a count of processes connected to the specified database name

select count(*) from master.dbo.sysprocesses
where dbid = db_id('databasename') 

This gives you unique logins connected to the specified database name

select distinct loginame from master.dbo.sysprocesses
where loginame = 'userlogin'
and dbid = db_id('databasename')

NOTE: if your application's processes use one login for all users then you
won't get a true value. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lonnie Johnson
Sent: Monday, April 02, 2007 4:52 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Users in SQL Server

You can do a passthrough query to the master database and execute this
system stored procedure. 

EXEC SP_WHO
 
May God bless you beyond your imagination!
Lonnie Johnson
ProDev, Professional Development of MS Access Databases Visit me at ==>
http://www.prodev.us


----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>; dba-sqlserver at databaseadvisors.com
Sent: Monday, April 2, 2007 6:31:59 AM
Subject: [AccessD] Users in SQL Server


Is there something that can be read from SQL Server to discover the count of
and even a list of users logged in to a SQL Server database?  I am moving a
client from an MDB data store to SQL Server and need to get this information
on demand, using VBA.

John W. Colby
Colby Consulting
www.ColbyConsulting.com