Retrieve USers connected to DB (1 Viewer)

Retrieve Users connected to DB

Hi,

I am trying to write a form which can list the users connected to the current SQL Server Database in Access, only problem is I cant find a way of doing this without having security impacts on the server.

I can use the following SQL to get a list
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'AutoCost2008'

but if I run this for any user who does not have admin role then they can only see there own user.

Does anyone know how I can get arround this or open a form in an ADP project with a different SQL user instead of the user setup in File/Connection.


Thanks,


Gavin,

Only way I can think of is to have a job that runs, say, every minute that truncates and populates a table that Access queries.

I think a better way would be not to query system tables at all, it would be better to build your own custom table, that is updated when a user opens the .adp and updated when the user closes the .adp.

If you query system tables you will have the following issues:

1. security hole
2. sleeping processes not accurately showing who is connected
3. the possibility of user accounts spawning multiple processes