Who is accessing your Database ?

We can identify that who are all accessing your Database with the various information as given below..

- When they connected
- From which machine name
- From which machine IP
- What program they using
- Login Name
- From which session
- Current request mode
- Active status
- What query thay finally applied

Here, I have used four DMOs - sys.dm_tran_locks, sys.dm_exec_sessions, sys.dm_exec_connections, sys.dm_exec_sql_text.

I try to identify that who are all accessing my SQLServerBuddy database.

SELECT DB_NAME(l.resource_database_id) 'Database Name',
l.request_mode 'Request Mode',
l.request_status 'Status',
l.request_session_id 'SessionID',
s.login_time 'Connected at',
c.client_net_address 'Client IP',
s.[host_name] 'Client Machine',
s.[program_name] 'Program Name',
s.login_name 'Login',
q.[text] 'Query Used'
FROM sys.dm_tran_locks l JOIN sys.dm_exec_sessions s
ON(L.request_session_id = S.session_id)
JOIN sys.dm_exec_connections c CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) q
ON(C.session_id = S.session_id)
WHERE l.resource_type = 'DATABASE'
AND l.resource_database_id = DB_ID('SQLServerBuddy')
AND l.request_session_id <> @@SPID

No comments:

Post a Comment