#Requires -Version 5.1
<#
    JbTecWiz Support Centre -- generated fix script

    Fault : SQL Server error 18456 -- login failed for user
    Fix   : Fix the default database problem
    Source: https://jbtecwiz.com/support/srv-sql-18456

    Run as     : SQL Server Management Studio
    Expect     : 25 minutes
    Risk       : low
    Reversible : yes

    WHEN THIS IS THE RIGHT FIX
    State 38 or 40 -- the login is fine but the database it wants is not.

    HOW TO UNDO IT
    ALTER LOGIN to the previous default database; DROP USER to remove a
    mapping.

    This script walks the fix one step at a time and asks before each
    one. Steps with no command are things you do yourself -- it prints
    those and waits. Run with -DryRun to print without executing.

    --------------------------------------------------------------------
    NO WARRANTY - USE AT YOUR OWN RISK

    This script is provided by JbTecWiz as-is and with no warranty of any
    kind, express or implied. You run it entirely at your own risk.

    JbTecWiz accepts no liability for any loss or damage arising from its
    use, including but not limited to data loss, downtime, or configuration
    changes that turn out to be wrong for your system.

    You are responsible for reading this script before running it, for
    satisfying yourself that it suits the machine in front of you, and for
    having a working backup first. Some steps cannot be undone.
    --------------------------------------------------------------------
#>

[CmdletBinding()]
param(
    # Print every step and command without running anything.
    [switch]$DryRun,
    # Do not ask before each step. Read the script first if you use this.
    [switch]$Unattended
)

$ErrorActionPreference = 'Stop'
$script:Failed = 0

function Write-Rule { param([string]$Text)
    Write-Host ''
    Write-Host ('-' * 70) -ForegroundColor DarkGray
    if ($Text) { Write-Host $Text -ForegroundColor Cyan }
}

function Show-Prose { param([string]$Text, [string]$Colour = "Gray")
    if (-not $Text) { return }
    $words = $Text -split "\s+"; $line = "  "
    foreach ($w in $words) {
        if (($line.Length + $w.Length) -gt 74) { Write-Host $line -ForegroundColor $Colour; $line = "  " }
        $line += "$w "
    }
    if ($line.Trim()) { Write-Host $line -ForegroundColor $Colour }
}

function Invoke-Step {
    param(
        [int]$Number, [string]$Do, [string]$Why,
        [scriptblock]$Command, [switch]$Manual, [string]$Shell = "powershell"
    )
    Write-Rule "  Step $Number of 6"
    Show-Prose $Do "White"
    if ($Why) { Write-Host ""; Show-Prose $Why "DarkGray" }

    if ($Manual) {
        Write-Host ''
        Write-Host '  -> Do this yourself, then press Enter to carry on.' -ForegroundColor Yellow
        if (-not $Unattended -and -not $DryRun) { [void](Read-Host) }
        return
    }

    Write-Host ''
    foreach ($l in ($Command.ToString().Trim() -split "`n")) {
        Write-Host ("    " + $l.Trim()) -ForegroundColor Green
    }
    Write-Host ''

    if ($DryRun) { Write-Host "  (dry run -- not executed)" -ForegroundColor DarkGray; return }
    if (-not $Unattended) {
        $a = Read-Host "  Run this step? [Y]es / [S]kip / [Q]uit"
        if ($a -match "^[Qq]") { Write-Host "  Stopped at your request."; exit 0 }
        if ($a -match "^[Ss]") { Write-Host "  Skipped." -ForegroundColor DarkGray; return }
    }
    try { & $Command }
    catch {
        $script:Failed++
        Write-Host ("  Step $Number failed: " + $_.Exception.Message) -ForegroundColor Red
        Show-Prose "The rest of the fix may depend on this. Read the write-up before carrying on." "Red"
        if (-not $Unattended) {
            $c = Read-Host "  Carry on anyway? [y/N]"
            if ($c -notmatch "^[Yy]") { exit 1 }
        }
    }
}

Write-Rule
Write-Host '  SQL Server error 18456 -- login failed for user' -ForegroundColor White
Write-Host '  Fix the default database problem' -ForegroundColor Cyan
Write-Host ''
Write-Host '  Risk: low   Reversible   25 minutes'
Write-Host ''
Show-Prose 'No warranty. Use at your own risk - JbTecWiz accepts no liability. Read it before you run it, and have a backup.' 'DarkYellow'
Write-Rule

if (-not $Unattended -and -not $DryRun) {
    $go = Read-Host '  Ready? [y/N]'
    if ($go -notmatch "^[Yy]") { Write-Host "  Nothing was changed."; exit 0 }
}

Invoke-Step -Number 1 -Do 'Find which database is being asked for.' -Command {
    cmd.exe /c 'EXEC xp_readerrorlog 0, 1, N''Login failed'', N''38'';'
}

Invoke-Step -Number 2 -Do 'Check the database exists and is online.' -Command {
    cmd.exe /c 'SELECT name, state_desc, user_access_desc, is_read_only FROM sys.databases ORDER BY name;'
}

Invoke-Step -Number 3 -Do 'Bring it online if it is not.' -Command {
    cmd.exe /c 'ALTER DATABASE [AppDb] SET ONLINE; & ALTER DATABASE [AppDb] SET MULTI_USER;'
}

Invoke-Step -Number 4 -Do 'Check the login''s default database -- a login pointing at a database that has been dropped fails before it can connect anywhere.' -Command {
    cmd.exe /c 'SELECT name, default_database_name FROM sys.server_principals WHERE type IN (''S'',''U'');'
}

Invoke-Step -Number 5 -Do 'Point it somewhere that exists.' -Command {
    cmd.exe /c 'ALTER LOGIN [appuser] WITH DEFAULT_DATABASE = [master];'
}

Invoke-Step -Number 6 -Do 'Map the login to a user in the database if it is not already.' -Command {
    cmd.exe /c 'USE [AppDb]; & CREATE USER [appuser] FOR LOGIN [appuser]; & ALTER ROLE db_datareader ADD MEMBER [appuser];'
}

Write-Rule "  Confirm it worked"
Show-Prose 'The application connects to the intended database.' 'White'
Write-Host ''
if (-not $DryRun) {
    cmd.exe /c 'SELECT DB_NAME() AS CurrentDb, SUSER_NAME() AS LoginName;'
}

Write-Rule
if ($script:Failed -gt 0) {
    Write-Host ("  Finished with " + $script:Failed + " failed step(s).") -ForegroundColor Yellow
    Show-Prose 'Read the full write-up at https://jbtecwiz.com/support/srv-sql-18456' 'Yellow'
} else {
    Write-Host '  Finished.' -ForegroundColor Green
}
Write-Host ''
Show-Prose 'To undo: ALTER LOGIN to the previous default database; DROP USER to remove a mapping.' 'DarkGray'
Write-Rule