CODEWITHSUNDEEP
sql-server-2008
Gali
Gali

Reputation: 14973

how to show which user connect to Database X in sql-server 2008?

How can I show which user connect to Database X in sql-server 2008 ?

I have many databases in my sql-server 2008 and I need to show only the users that connect and works on database X

Thanks in advance

Upvotes: 0

Views: 9286

Answers (3)

DontCopyThatFloppy
DontCopyThatFloppy

Reputation: 63

@gabriel-adams provides a nice script over on this similar question. If you're like me and wandered here from a Google Search, this may save you a few clicks.

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

Upvotes: 0

Manuk
Manuk

Reputation: 1

You can also use Ctrl+Alt+A to open recourse monitor and see which user is connected to your Database X

Upvotes: 0

Tomas McGuinness
Tomas McGuinness

Reputation: 7689

Look at the procedure sp_who - http://msdn.microsoft.com/en-us/library/ms174313.aspx

Upvotes: 1

Related Questions