CODEWITHSUNDEEP
c#sql-serverpowershellsmo
Primoz
Primoz

Reputation: 4331

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

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'

Upvotes: 0

Views: 3328

Answers (2)

alroc
alroc

Reputation: 28194

Normally I'd suggest sp_who2 but its output doesn't work with PowerShell. However, sp_who will.

invoke-sqlcmd -server $SERVERNAME -query "exec sp_who"|format-table -autosize

Or if you want just the hostnames

invoke-sqlcmd -server $SERVERNAME -query "exec sp_who"|select-object -property hostname -unique | format-table -autosize;

If you don't have the SQLPS module available, substitute in the appropriate ADO.NET methods.

Upvotes: 1

Jon Boulineau
Jon Boulineau

Reputation: 451

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

Upvotes: 1

Related Questions