Returning SQL Server Service Account Information Using Powershell


Guys, these days our friend Marcos Freccia (blog | twitter) asked me a way to verify which account the SQL Agent service is running. This is simple to do if you have one server.

And if you have have 10, 20. 100. Complicated ? Not so much …

Formiga-Atomica_thumb[1]

Up and At ’em, Atom Ant

I showed this simple script :

(Credits to my good friend  and Powershell MVP Jedi Ravikanth Chaganti (blog | twitter))

   1: Get-WmiObject -Class Win32_Service -Filter "Name='SQLSERVERAGENT'" | select StartName

and it really works, both local and remote (add -computername parameter)

but we could improve it, I want to filter which servers, which instances (or all) and also show that the account is member of Local Admin group.

I confess that I started thinking about my script on the following logic:

  • access servers
  • access  instances
  • For each one I access the account data and display (foreach – and other foreach – and other foreach …)

That’s when I remembered an article by Don Jones (blog | twitter) :

ForEach Makes me Die a Little Inside

And it is true, in my logic above I’m thinking like C #, VB or any other programming language. Not like PowerShell. If it is to do so,why to use PowerShell?

Even this was a subject that I discussed with my Mentor Chad Miller because many SQL server professionals are blogging today about PowerShell, but not coding in PowerShell.

Forget that everything in PowerShell is object and that we have to help us ..the Wonderful Pipeline

So I changed my logic to use the Pipeline. How?, Using functions and linking this functions in Powershell CustomTables. Is the best ou correct way ? I dont know, I am not a Powershell Specialist, but I think I am on the correct line Alegre.

So my code was :

 

   1:  

   2: function get-ServiceIsAdmin 

   3: {

   4:     [CmdletBinding()]

   5:  

   6:     Param (

   7:         [Parameter(position=0,Mandatory = $true,ValueFromPipeline = $true )][String] $ComputerName,

   8:         [Parameter(position=1,Mandatory = $true)][String] $ServiceAccount

   9:     )

  10:     process {

  11:     

  12:                 $ServiceAccountSplit = $ServiceAccount -split '\\'

  13:                 if ($ServiceAccountSplit.count -eq 1)

  14:                     { $ServiceAccountToUse = $ServiceAccountSplit[0] }

  15:                 else

  16:                     { $ServiceAccountToUse = $ServiceAccountSplit[1] }

  17:                 

  18:                 #Original Script in 

  19:                 #http://www.hanselman.com/blog/HowToDetermineIfAUserIsALocalAdministratorWithPowerShell.aspx

  20:                 

  21:                 $administratorsAccount = Get-WmiObject -ComputerName "$ComputerName" Win32_Group -filter "LocalAccount=True AND SID='S-1-5-32-544'" 

  22:                 $administratorQuery = "GroupComponent = `"Win32_Group.Domain='" + $administratorsAccount.Domain + "',NAME='" + $administratorsAccount.Name + "'`""

  23:                 $user = Get-WmiObject Win32_GroupUser -filter $administratorQuery | select PartComponent | where {$_ -match $ServiceAccountToUse}

  24:                 if ($user -eq $null ){  'No' }    else    {  'Yes' }

  25:                 

  26:     

  27:     }

  28:         

  29:  

  30: }

  31: function Get-SQLServerServiceInfo  {

  32:  

  33:  

  34:     [CmdletBinding()]

  35:  

  36:     Param (

  37:         [Parameter(position=0,Mandatory = $true,ValueFromPipeline = $true )][String] $ComputerName,

  38:         [Parameter(position=1,Mandatory = $true )][String] $SQLServerInstance = 'All',

  39:         [ValidateSet("Engine", "Agent")]

  40:         [Parameter(position=2,Mandatory = $true )][String] $ServiceType = 'Engine'

  41:  

  42:  

  43:     )

  44:     

  45:     begin {

  46:         

  47:         [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 

  48:         [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

  49:         

  50:         Function Get-ServiceByInstance ($ServiceByInstance,$InstanceName,$ReturnName) 

  51:         {

  52:             if ( -not $ReturnName) {

  53:                 [string] $ServiceAccount = ($ServiceByInstance    | where {$_.Displayname -like "*$($InstanceName)*"}    | select ServiceAccount)

  54:                 Write-Output    ($ServiceAccount.substring(17,$ServiceAccount.length  - 18))

  55:             } else {

  56:                 [string] $ServiceAccount = ($ServiceByInstance    | where {$_.Displayname -like  "*$($InstanceName)*"}    | select  Name)

  57:                 Write-Output ($ServiceAccount.substring(7,$ServiceAccount.length  - 8))

  58:         

  59:             }

  60:                 

  61:         }    

  62:     }

  63:     Process {

  64:             try {

  65:                 $ManagedComputer = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') "$ComputerName"

  66:                 $ServiceTypeShow = if ($ServiceType -eq 'Agent') {'sqlagent'} else {'SqlServer'}

  67:  

  68:                 if ($SQLServerInstance -eq 'All') { 

  69:                     $ServiceByInstance =($ManagedComputer.Services | where { $_.type -eq "$($ServiceTypeShow)"} | select DisplayName,ServiceAccount,Name)

  70:  

  71:                     $ManagedComputer.ServerInstances | select     @{Expression={$($ComputerName) };Label = "ComputerName"} ,

  72:                                                                 @{Expression={$($_.NAME) };Label = "SQLEngineInstanceName"}, 

  73:                                                                 @{Expression={Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name)  -ReturnName $False };Label = "ServiceAccount"},

  74:                                                                 @{Expression={get-ServiceIsAdmin -ComputerName $ComputerName -ServiceAccount (Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name)) };Label = "IsLocalAdmin"},

  75:                                                                 @{Expression={Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name)  -ReturnName $True };Label = "ServiceName"} 

  76:  

  77:  

  78:                 } else { 

  79:                 

  80:                     $ServiceByInstance =($ManagedComputer.Services | where { $_.type -eq "$($ServiceTypeShow)" -and $_.displayname -like "*$SQLServerInstance*" } | select DisplayName,ServiceAccount,Name) 

  81:                     $ManagedComputer.ServerInstances | where {$_.name -eq $SQLServerInstance }  | select     @{Expression={$($ComputerName) };Label = "ComputerName"},

  82:                                                                                                             @{Expression={$($_.NAME) };Label = "SQLEngineInstanceName"},

  83:                                                                                                             @{Expression={Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name) -ReturnName $false};Label = "ServiceAccount"},

  84:                                                                                                             #@{Expression={get-ServiceIsAdmin -ComputerName $ComputerName -ServiceAccount (Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name)) };Label = "IsLocalAdmin"},

  85:                                                                                                             @{Expression={Get-ServiceByInstance -ServiceByInstance $ServiceByInstance -InstanceName $($_.Name)  -ReturnName $True };Label = "ServiceName"}

  86:  

  87:                 }

  88:                     

  89:             } catch {

  90:                 Write-Output $Error[0]

  91:             }

  92:     

  93:     }

  94: }

Download [updated]

Many thanks to Scott Hanselman (blog | twitter) , I used your script to check if the account is member of Local Admin group

 

How to Determine if a User is a Local Administrator with PowerShell

the properties returned are:

  • Computer: Computer Name
  • SQLEngineInstanceName: SQL Server Instance Name
  • ServiceAccount: Service Account used by SQL Server Service.
  • IsSysAdmin: If ServiceAccount is the Adm Local Machine or Not.
  • [Updated]

    • property output ServiceName = Service Name.
    • and new input Parameter ServiceType : Engine = SQL Server Service and Agent = SQL Server Agent.

     

So, lets start to Play Alegre

Example 1: I want the information of all instances of my machine (local)

   1: Get-SQLServerServiceInfo  -SQLServerInstance all

Example 2: I want information instance INST1 of my machine (local)

   1: Get-SQLServerServiceInfo  -SQLServerInstance INST1

Example 3: I want the information from the machine Server2, and all instances

   1: Get-SQLServerServiceInfo -Computername Server2 -SQLServerInstance All

And to Scaling out this code ?

   1: "Server1","Server2", "Server3" | Get-SQLServerServiceInfo  -SQLServerInstance All

or

   1: Get-Content c:\temp\servers.txt | Get-SQLServerServiceInfo  -SQLServerInstance All

Can you see this ? Can you feel this ? Can you feel the Power ?

POWERSHELL ROCKS !!!

No matter how many times that you told me you wanted to leave
No matter how many breaths that you took, you still couldn’t breathe
No matter how many nights that you’d lie wide awake to the sound of the poison rain
Where did you go
Where did you go
Where did you go

As days go by the night’s on fire

Tell me, would you kill to save a life
Tell me, would you kill to prove you’re right?
Crash, crash, burn, let it all burn
This hurricane’s chasing us all underground

No matter how many deaths that I die, I will never forget
No matter how many lies that I live, I will never regret
There is a fire inside of this heart and a riot about to explode into flames
Where is your God
Where is your God
Where is your God

Hurricane

30 Seconds To Mars

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL. Bookmark the permalink.

6 Responses to Returning SQL Server Service Account Information Using Powershell

  1. Great Post my friend !!!

    I don’t have words to describe how many times powershell saved my life, and you too of course, always available to help me. Thanks for that.

  2. One thing I would change is using a Boolean (True/False) instead of Yes/No.

  3. I am impressed by the quantity of information on this website. there are a lot of good resources here. I am sure i will visit again soon.

  4. Edgardo says:

    Hi! can u upload again the script?

    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s