Query user/worlstation access of database

I have a client who needs to be able to run a query that shows a list of all
workstations (IP address, logon etc) who are currently accessing a specified
SQL database with logon / last activity time (if possible).
Most of the users are using a web application to access the database, so how
would we identify which users are accessing the database through a web
server. There are some however who are using the fat client application to
access the same database.
Is this possible?see if this is what you want?
select * from sysprocesses
--
"Mark - HIS" wrote:

> I have a client who needs to be able to run a query that shows a list of a
ll
> workstations (IP address, logon etc) who are currently accessing a specifi
ed
> SQL database with logon / last activity time (if possible).
> Most of the users are using a web application to access the database, so h
ow
> would we identify which users are accessing the database through a web
> server. There are some however who are using the fat client application to
> access the same database.
> Is this possible?
>|||If using SQL Server 2000 (not sure about the 2005 equivalent) you can
view this info through the GUI: Under the "Management" node there
should be a node called "Current Activity" and under that a node called
"Process Info" & this is the one you want. You should click on this
node to get a display that contains, among other things, the following
info:
* The SQL Server login that is associated with a process
* The host that the process connected from (if access is via a web
server the entry for the host should be the name of the web server)
* Time logged in
* Time of last batch/command execution
* Command that was last executed (double click on a process for this
information)
In SQL Server 2000 you have the sp_who (you also have the undocumented
sp_who2 stored proc in SQL Server 2000, not sure if it's in SQL Server
2005) which will materialize a result set. In this result set, the
following may be of interest to you:
* loginname - The SQL Server login associated with a process
* hostname - The host the process connected from
In addition, sp_who2 provides a column (lastbatch) that gives the date
and time when the last command/batch is executed for a process.
Quick example calls:
EXEC sp_who
GO
EXEC sp_who2
GO
Hope that helps a bit|||This table is in the master database.
So use
select * from master.dbo.sysprocesses.|||Anyone connecting to the web server will show up as the network user that
the web page is running under. If you disable anonymous access and have the
users login with their network credentials, I think these MAY show up as the
network account in SQL Server, but I have not tried this myself, so I could
be wrong.
The IP address will always be the web server itself, since that is where the
connection is made.
The SQL Server user will show up as whatever login the web server is using
to connect to the database. If you are using the same connection string for
all users, then that is the ID that will show up.
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:48AAC7C6-FEA9-4090-9A85-DC502F55EE86@.microsoft.com...
> I have a client who needs to be able to run a query that shows a list of
all
> workstations (IP address, logon etc) who are currently accessing a
specified
> SQL database with logon / last activity time (if possible).
> Most of the users are using a web application to access the database, so
how
> would we identify which users are accessing the database through a web
> server. There are some however who are using the fat client application to
> access the same database.
> Is this possible?
>