[AccessD] Users in SQL Server

Also, note that for best scalability and best practices, users should
connect to the database, do what they need, then disconnect.  So really, the
users should not be connected to the database all of the time as in Access.

Bobby 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Monday, April 02, 2007 10:47 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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