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