service account infomation from sql instance

Hello

Can I get all the SQL related services(sql server,agent,reporting server etc) information like serviceaccount being used,status by passing sql instance name and not the windows server name?

I can get using windows machine name using

gwmi win32_service -computername but if I pass instance name it fails.

Appreciate your help

Parents
No Data
Reply
  • Yes, you can, but it'll require either best guess, or SMO. Get-WmiObject and Get-Service require the proper NetBIOS name of the SQL Server. Your query may actually be as simple as this

    $sqlservices = @()
    $sqlserver = "sql2005express\sqlexpress"
    $netname = $sqlserver.Split("\")[0]
    $instance = $sqlserver.Split("\")[1]
    if ($instance -eq $null) { $instance = "MSSQLSERVER" }

    $services = Get-WmiObject Win32_Service -ComputerName $netname | Where-Object { $_.Caption -like "*$instance*" }

    foreach ($service in $services) {
    $sqlservices += [PSCustomObject]@{
    Name = $service.Caption
    State = $service.State
    ServiceAccount = $service.StartName
    }
    }

    $sqlservices

    Or if you are using a bunch of aliases that won't work with Wmi well, you can use ManagedComputer, which is found within SMO and the SDK (basically, it'll work if you have SQL Server Management Studio installed). ManagedComputer is the SMO equivalent of SQL Configuration Manager.

    $sqlservices = @()
    # Load up assemblies
    [void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
    [void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

    $sqlserver = "sql2005express\sqlexpress"
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver

    $computer = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $server.NetName
    $services = $computer.Services

    foreach ($service in $services) {
    $sqlservices += [PSCustomObject]@{
    Name = $service.DisplayName
    State = $service.ServiceState
    ServiceAccount = $service.ServiceAccount
    }
    }

    $sqlservices

     

     

Children
No Data