Trevor: Best Wishes From Cleveland

Hi Trevor,
We are no place exotic, but we are thinking of you and wishing you a speedy recovery.
Here's a picture of us in our backyard in Cleveland, Ohio.

(Brian, Jenn, Tyler, Nate)

And another, warmer picture, from Devil's Lake, Michigan.

Hoping you feel better soon!
The Davis Family

TSQL Tuesday #15: Automation

Now that I've started blogging I decided to throw my hat into the ring for TSQL Tuesday, hosted this month by Pat Wright aka SQLAsylum (Blog | Twitter) and the topic is Automation.

Recently, I needed to compile a listing of all the accounts that were running all of the SQL Server services in the environment.  After throwing the manual option out the window and  looking at a few other options I decided that PowerShell would provide the best solution.  In my environment I have dedicated database that houses information I've collected about all of the servers.  One of the tables just happens to contain a list of servers.  Using PowerShell I'll scan through the list of servers and then run a WMI query against each to get the accounts for each SQL service on the server.  Once I have the accounts and their associated services I then upload that information to a table in my database.  From there the data can be exported or displayed via SSRS.

The code is pretty well documented so let's take a look at the script (download link at end of post).

# =======================================================================
#  SCRIPT BODY
# =======================================================================
#Initialize the SQL Environment
.\Initialize-SqlpsEnvironment.ps1

#Define Counter Variables
[int]$count=0
[int]$total=0

#Define the destination server and database names
$sqlsrv = "SQLSrv01"
$destdb = "SvcDB"

#Clean out the SvcInfo Table
$q = "delete LoadSvcInfo"
$reslist = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
Write-Host "Clean SvcInfo Table"

#Define the list server and database
$listsrv = "SQLSrv01"
$listdb = "ServerDB"

#Get server list
$q = "select distinct servername from ServerList order by servername"
$reslist = invoke-sqlcmd -ServerInstance $listsrv -Database $listdb -Query $q
$total = $reslist.length
foreach ($strSrv in $reslist){
#Increment Counter
$count++

#Display current server & count
Write-Host "Server: " $strSrv.ServerName " " $count " of " $total

#$m = New-Object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $strSrv.ServerName

#Get Service Account Info
$colItems = gwmi win32_service -computer $strSrv.ServerName -property name, startname, caption | where {$_.Name -like "*SQL*"}
foreach($objItem in $colItems) {

#Send info to our database
$q = "insert [SvcDB].[dbo].[SvcInfo] (SrvName, SvcName, SvcCaption, SvcAcct) Values ("
$q = $q + "'" + [string]$strSrv.ServerName + "', "
$q = $q + "'" + [string]$objItem.name + "', "
$q = $q + "'" + [string]$objItem.caption + "', "
$q = $q + "'" + [string]$objItem.startname + "')"
$res = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
}

$colItems = gwmi win32_service -computer $strSrv.ServerName -property name, startname, caption | where {$_.Name -like "MSDTSServer*"}
foreach($objItem in $colItems) {

#Send info to our database
$q = "insert [SvcDB].[dbo].[SvcInfo] (SrvName, SvcName, SvcCaption, SvcAcct) Values ("
$q = $q + "'" + [string]$strSrv.ServerName + "', "
$q = $q + "'" + [string]$objItem.name + "', "
$q = $q + "'" + [string]$objItem.caption + "', "
$q = $q + "'" + [string]$objItem.startname + "')"
$res = invoke-sqlcmd -ServerInstance $sqlsrv -Database $destdb -Query $q
}
#$m = $null
}
Write-Host "DONE!"
#=======================================================================
#  END OF SCRIPT: [ServiceAccounts]
#=======================================================================

Download Zip of ServiceAccounts Script