In a SQL Server environment, it can be useful to monitor the user activity and track who is connected to the databases, from which machine they are connected, when they connected and what application they are running. In this blog, we will discuss a script that shows the actual usernames and machines that are connected to a SQL Server instance and provides information about their login time and the application they are running.
The following query can be used to retrieve this information:
SELECT @@ServerName AS SERVER
,NAME
,login_time
,STATUS
,hostname
,program_name
,nt_username
,loginame
FROM sys.databases d
LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE NAME not in ('master','model','msdb','tempdb')
-- WHERE NAME = 'ABCD' -- Database Name
AND loginame IS NOT NULL order by 2