In the event that you have lost or forgotten your SQL administrator password, the following script can create an additional administrator SA user account that you can use to log in.  This is preferable over resetting the SA password because changing the password would break any applications that had the old password stored.

 

$InstanceName = 'MSSQLServer'
$NewUser = 'sa2'
$NewPassword = '12AeAOuaOE,.'

#--------------------

$Instance_List_Path = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\"


if(!$InstanceName){
    $Instances = (Get-Item $Instance_List_Path).Property
    if($Instances.Count -gt 1){
        Write-Host "No instance name specified.  Valid values are:"
        Write-Host $Instances
    } elseif ($Instances.Count -eq 1) {
        $InstanceName = $Instances;
        Write-Information "SQL Instance not specified.  Using $($InstanceName)"
    } else {
        Write-Host "Unable to detect SQL instances."
    }
}

if($InstanceName){

    $ServiceDisplayName = "SQL Server ($($InstanceName))"
    $ServiceName = Get-Service -DisplayName $ServiceDisplayName


    $InstanceLocation = Get-ItemPropertyValue $Instance_List_Path -Name $InstanceName

    $InstanceParameters = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$($InstanceLocation)\MSSQLServer\Parameters"
    $ArgName = "SQLArg99"

    Set-ItemProperty -Path $InstanceParameters -Name $ArgName -Value "-m"

    Stop-Service $ServiceName -Force
    Start-Service $ServiceName

    Start-Sleep -Seconds 1

    if($InstanceName -ieq 'MSSQLServer'){
        $ServerInstance = "(local)"
    } else {
        $ServerInstance = "(local)\$($InstanceName)"    
    }

    $Query = "
    --Enable Administrator Access
    CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS
    GO

    ALTER SERVER ROLE sysadmin ADD MEMBER [BUILTIN\Administrators]
    GO

    --Enable Regular User Access
    --CREATE LOGIN [BUILTIN\Users] FROM WINDOWS
    --GO

    ALTER SERVER ROLE sysadmin ADD MEMBER [BUILTIN\Users]
    GO
    
    --Create a secondary admin user
    CREATE LOGIN [$($NewUser)] WITH PASSWORD=N'$($NewPassword)', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [$($NewUser)]
    GO
    "
    Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $Query

    Remove-ItemProperty -Path $InstanceParameters -Name $ArgName
    Stop-Service $ServiceName -Force
    Start-Service $ServiceName

}