sql-server: how do i know who is in my database?

Question

i have an access front end and sql server backend. i would like to know which users are currently using the database. is this possible to do using access or sql-server?

Answer 1

In SQL Server, you can run this stored procedure:

sp_who2

EDIT:

You can use this query if you want to see who is using your server at any given time. This will allow you to further filter at will.

SELECT
    SessionId           = ses.session_id
    ,[Database]    = DB_Name(er.database_id)
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,StartTime          = er.start_time
    ,ClientAddress      = con.client_net_address
    ,SQLStatement       = st.text
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
WHERE ses.is_user_process = 0x1
AND ses.session_id != @@SPID
ORDER BY
    ses.session_id

Answer 2

For SQL Server you can try

SELECT * 
FROM sys.dm_exec_sessions

which will list all the open sessions