SQL Service account status with PowerShell


One of my client has the requirement to have SQL Service account running with domain account and should not be with local account.

You may receive SSPI error after changing it to domain account. In order to fix that issue check my previous blog "How to register SPN for SQL service account"


#Change value of following variables as needed 
$ServerList = Get-Content "D:SCMSSQLServer.txt" 
$OutputFile = "D:SCMSSQLRecoverModel_MDA_$((Get-Date).ToString('ddMMyyyy_hhmm')).htm" 
$HTML = '<style type="text/css"> 
   table{font-family: Calibri,Candara,Segoe,Segoe UI,Optima,Arial,sans-serif;width:100%; border: black;  border-style: double;  border-width: 2px;}
   table td,table th{font-size:1em;border:1px solid #98bf21;padding:3px 7px 2px;} 
   table th{font-size:1.1em;padding-top:5px;padding-bottom:4px;background-color:#81BEF7;color:#fff} 
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header> 
	    <TH align=center><B>Date</B></TH>	
            <TH align=center><B>DatabaseName</B></TH> 
            <TH align=center><B>RecoveryModel</B></TH> 
            <TH align=center><B>PageVerify</B></TH> 
	    <TH align=center><B>AutoClose</B></TH> 
	    <TH align=center><B>AutoShrink</B></TH> 
	    <TH align=center><B>DatabaseOwner</B></TH>
<TH align=center><B>AutoCreateStatisticsEnabled</B></TH>
	    <TH align=center><B>DB CreateDate</B></TH>
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
ForEach ($ServerName in $ServerList) 
    $HTML += "<TR bgColor='#81F7D8'><TD colspan=9 align=center><B>$ServerName</B></TD></TR>" 
    $SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName  
    Foreach($Database in $SQLServer.Databases | where-object {$_.Name -ne "tempdb" -and $_.Name -ne "master" -and $_.Name -ne "ReportServerTempDB" -and $_.Name -ne "ReportServer$SGSI2W0256TempDB" -and $_.Name -ne "ReportServer$SGSI2W0256"}) 

IF ($SimpleRecoveryModel -eq 'simple')
$color1 = 'red'
ELSE {$color1 = 'Green'}
IF ($PageVerify -ne 'Checksum')
$color2 = 'red'
ELSE {$color2 = 'Green'}
IF ($AutoClose -eq 'True')
$color3 = 'red'
ELSE {$color3 = 'Green'}
IF ($Autoshrink -eq 'True')
$color4 = 'red'
ELSE {$color4 = 'Green'}
IF ($DBOwner -eq "" -Or $DBOwner -Like "*sadm*")
$color5 = 'yellow'
ELSE {$color5 = 'Green'}
IF ($AutoCreateStatisticsEnabled -eq 'True')
$color6 = 'green'
ELSE {$color6 = 'red'}

 IF ( $color1 -eq 'red' -or $color2 -eq 'red' -or $color3 -eq 'red' -or $color4 -eq 'red' -or $color5 -eq 'red' -or $color6 -eq 'red')
		$HTML += "<TR> 
                    <TD BGCOLOR='$color1' ALIGN=CENTER>$($Database.RecoveryModel)</TD> 
		    <TD BGCOLOR='$color2' ALIGN=CENTER>$($Database.pageverify)</TD>
		    <TD BGCOLOR='$color3' ALIGN=CENTER>$($Database.autoclose)</TD>
		    <TD BGCOLOR='$color4' ALIGN=CENTER>$($Database.autoshrink)</TD>
		    <TD BGCOLOR='$color5' ALIGN=CENTER>$($Database.owner)</TD>
<TD BGCOLOR='$color6' ALIGN=CENTER>$($Database.AutoCreateStatisticsEnabled)</TD>
<TD BGCOLOR='green' ALIGN=CENTER>$($Database.createdate)</TD>


$HTML += "</Table></BODY></HTML>" 
$HTML | Out-File $OutputFile