In SQL Server there are always DMV’s and stored proc’s available to identify active connections to a database. One such inbuilt stored procedure that result sets the connections of a database is sp_who2. Also one of the most commonly used DMV that provides us with who is actively connected to a database at any given point of time is sys.dm_exec_connections dynamic management view. Even though this DMV provides us with information like most recent sql handle it does not provide the actual sql,the database name that the session is connected to or the object name. But this can be achieved by cross applying the result set of the latter DMV with the table valued function SYS.DM_EXEC_SQL_TEXT passing the most recent sql handle as an argument. The query is posted below:
SELECT DMEC.SESSION_ID, DMEC.MOST_RECENT_SESSION_ID, DMEC.CONNECT_TIME, DMEC.LAST_READ, DMEC.LAST_WRITE, DMEC.NUM_READS, DMEC.NUM_WRITES, DMEC.CONNECTION_ID, DMEC.PARENT_CONNECTION_ID, DMEC.MOST_RECENT_SQL_HANDLE, CASE WHEN DMEST.DBID = 32767 THEN 'RESOURCEDB' ELSE DB_NAME(DMEST.DBID) END AS DATABASE_NAME, CASE WHEN DMEST.DBID IS NULL THEN NULL ELSE OBJECT_SCHEMA_NAME(DMEST.OBJECTID, DMEST.DBID) END AS OBJECT_SCHEMA_NAME, CASE WHEN DMEST.DBID IS NULL THEN NULL ELSE OBJECT_NAME(DMEST.OBJECTID, DMEST.DBID) END AS OBJECT_NAME, DMEST.TEXT AS 'SQLText' FROM SYS.DM_EXEC_CONNECTIONS DMEC CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DMEC.MOST_RECENT_SQL_HANDLE) DMEST
Hope this helps.