Gets List of Applications, Users and Host Names from SQL Server Default Trace and Exports to JSON Output Sample { "ServerName": "MYSERVERNAME\MYINSTANCENAME", "DatabaseName": "MYDATABASENAME", "DatabaseID": 5, "NTUserName": "MYNTUSERNAME", "NTDomainName": "MYNTDOMAIN", "HostName": "MYSERVERNAME", "ApplicationName": "LiteSpeed for SQL Server", "L…

Get-SQLServerDatabaseUsers.ps1

## Invoke by Running  .\Get-SQLServerDatabaseUsers.ps1 SQLSERVER\INSTANCENAME
## Gets List of Applications, Users and Host Names from SQL Server Default Trace
param(
  [string] $ServerInstance = $(Throw "Provide a SQL Server Instance Name as first parameter")
)
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO'); 
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$QueryApplications = "SELECT DISTINCT t.ServerName,t.DatabaseName, t.DatabaseID,t.NTUserName,t.NTDomainName,t.HostName,t.ApplicationName,t.LoginName,t.DBUserName,t.LinkedServerName,t.SessionLoginName
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value] FROM    sys.fn_trace_getinfo(NULL) f WHERE   f.property = 2 )), DEFAULT) T"
$QueryApplicationsResults = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $QueryApplications
$QueryApplicationsResults | Select-Object ServerName,DatabaseName,DatabaseID,NTUserName,NTDomainName,HostName,ApplicationName,LoginName,DBUserName,LinkedServerName,SessionLoginName | ConvertTo-JSON