Is it possible to get hostnames of computers connected to MS SQL database?

Question

with following code you list all users that are connected to certain DB

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$Sql = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “DEVSQL”
$DB = $Sql.Databases.Item("TestDB")
$DB.Users

Is there a way to get hostnames of the computers that users are connected from ? Something like query below.

select distinct hostname, loginame, db_name(dbid)
from master..sysprocesses 
where db_name(dbid) = 'TestDB'

Answer 1

First, the PowerShell code you posted returns ALL database users, not just the ones that are currently connected.

In order to get the host and login info for current processes use this code:

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$Sql = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “DEVSQL”
$sql.EnumProcesses() | Where_Object {$_.Database -eq "TestDB"} | Select host,login