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"
Output:
#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}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
<TR>
<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>
</TR>"
[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"})
{
$DatabaseName=$Database.Name
$SimpleRecoveryModel=$Database.RecoveryModel
$PageVerify=$Database.pageverify
$AutoClose=$Database.autoclose
$Autoshrink=$Database.autoshrink
$DBOwner=$Database.owner
$AutoCreateStatisticsEnabled=$Database.AutoCreateStatisticsEnabled
$CreateDate=$Database.CreateDate
$EndDate=Get-Date
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>$(get-date)</TD>
<TD>$($Database.Name)</TD>
<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>
</TR>"
}
}
}
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile 
As a part of my DBA activities, I do a lot of SQL Server installations every week. Most of the time, I install the instance with the default collation. A collation is a configuration setting in SQL Server that determines how the database engine should read the data. SQL Server has huge list of collations […]
2022-12-26 (first published: 2020-08-03)
17,615 reads
I bet, each DBA would change some settings after the SQL Server installation as per their company standard . I wrote this PowerShell script for one of my client who has below standards: Port should be 1433, static Disable SQL telemetry* & SQLWriter services Change recovery model to FUll for all databases except tempdb and […]
2020-07-02 (first published: 2020-06-21)
2,505 reads
I have created this script for one of our client, the requirement was all the databases should be in full recovery model and should be monitored. Also, I added other important parameters like AutoShrink, AutoClose etc. Output:
2020-06-16
1,027 reads
2020-06-15
43 reads
Check if the SPN is already registered: setspn -l domain\xxxxx If not, run below commands: setspn -A MSSQLSvc/abc.xx.companyname.com:1433 domain\xxxxx setspn -A MSSQLSvc/abc.xx.companyname.com domain\xxxxx setspn -A MSSQLSvc/abc:1433 domain\xxxxx setspn -A MSSQLSvc/ abc domain\xxxxx Verify again, setspn -l domain\xxxxx
2020-06-17 (first published: 2020-06-15)
6,715 reads