Fix SharePoint Server (on-prem) Database Permissions using PowerShell

For some time now, when reinstalling SharePoint Server (2019 or Subscription Edition), I have noticed that after creating the ConfigDB and Central Administration content database, the next steps get stuck:


The farm is being upgraded in the timer service process. The task is 10.00% completed.

Here I’m running this script as as “second step” in farm configuration.

Add-PSSnapin Microsoft.Sharepoint.Powershell -ea 0

Get-SPFarm 

. psconfig.exe -cmd upgrade -inplace b2b


Initialize-SPResourceSecurity

Install-SPService

Install-SPFeature -AllExistingFeatures -Verbose

At some point I realized that SharePoint no longer copes well if an account other than the farm account is entered as DBO for a database. – This fails especially during updates.

Here is a script I use to fix the database permissions. It works generically. You don’t actually have to change anything.

Add-PSSnapin microsoft.sharepoint.powershell -ea 0

$SPFarmAccountName =  $farm.TimerService.ProcessIdentity.Username

Get-SPDatabase | % {
	$db = $_

	write-host "Check database '$($db.Name)'"

	$cnn = New-Object System.Data.SqlClient.SqlConnection
	$cnn.add_InfoMessage([System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event);Write-Host $event.Message;}); 
	$cnn.FireInfoMessageEventOnUserErrors = $true
	$cnn.ConnectionString = $db.DatabaseConnectionString
	$cnn.Open()

	$cmd = New-Object System.Data.SqlClient.SqlCommand
	$cmd.Connection = $cnn
	$cmd.CommandTimeout = 7200
	$cmd.CommandText = "SELECT SUSER_SNAME(sid) as winloginname, * FROM sys.database_principals where (type='S' or type = 'U') and name='dbo'"
	$r = $cmd.ExecuteReader()

	$l = @()
	while($r.Read()) {
		$l += $r.GetString(0)
	}

	$currentDbo = $l | Select-Object -First 1

	write-host "`t current dbo: $currentDbo"

	$r.Close()
	$cmd.Dispose()

	if( $currentDbo -notlike "*$($SPFarmAccountName)*" ) {
		$cmd = New-Object System.Data.SqlClient.SqlCommand
		$cmd.Connection = $cnn
		$cmd.CommandTimeout = 7200
		$cmd.CommandText = @"
-- change database owner
EXECUTE AS LOGIN = '$($currentDbo)'
DROP SCHEMA IF EXISTS [$($SPFarmAccountName)];
DROP USER IF EXISTS [$($SPFarmAccountName)];
ALTER AUTHORIZATION ON DATABASE::[$($db.Name)] TO [$($SPFarmAccountName)];
REVERT               
"@
		$cmd.ExecuteNonQuery() | out-null

		$cmd = New-Object System.Data.SqlClient.SqlCommand
		$cmd.Connection = $cnn
		$cmd.CommandTimeout = 7200
		$cmd.CommandText = @"
-- take ownership
EXECUTE AS LOGIN = '$($SPFarmAccountName)'
DROP SCHEMA IF EXISTS [$($currentDbo)];
DROP USER IF EXISTS [$($currentDbo)];
CREATE USER [$($currentDbo)];
GRANT TAKE OWNERSHIP TO [$($currentDbo)];
ALTER ROLE [db_owner] ADD MEMBER [$($currentDbo)];
REVERT              
"@
		$cmd.ExecuteNonQuery() | out-null


		$cmd = New-Object System.Data.SqlClient.SqlCommand
		$cmd.Connection = $cnn
		$cmd.CommandTimeout = 7200
		$cmd.CommandText = "SELECT SUSER_SNAME(sid) as winloginname, * FROM sys.database_principals where (type='S' or type = 'U') and name='dbo'"
		$r = $cmd.ExecuteReader()

		$l = @()
		while($r.Read()) {
			$l += $r.GetString(0)
		}

		$newDbo = $l | Select-Object -First 1

		write-host "`t new dbo: $newDbo"

		$r.Close()
		$cmd.Dispose()
	}

	$cnn.Close()
}

The currently set “DBO” will become a regular user of the database and member of role “db_owner”.

Push large repository to GitHub using PowerShell

To push a large repository to GitHub is’s one approach to do it step by step: Only some commits at a time.

Here is how I do it using Powershell:


$s = . git --no-pager log --all --no-abbrev-commit --pretty=format:%H,%S,%cI 

$s | % {
    $ps = $_.Split(",", [System.StringSplitOptions]::RemoveEmptyEntries)  

    $hash = $ps[0]
    $ref = $ps[1]
    $date = [datetime]::parse($ps[2])

    return (New-Object PSObject -Property @{
        Hash = $hash
        Ref = $ref
        Date = $date
    })
} | sort-object Date | where-object {$_.Ref -like "refs/heads/*" } | ForEach-Object {

    $ref = $_.Ref
    $date = $_.Date
    $hash = $_.Hash


    write-host "Branch: $ref  Date: $($date)"

    $outp = . git push --no-verify github "$($hash):$($ref)"  2>&1

    if( $lastexitcode -ne 0 -and ($null -eq ($outp | ? { $_ -like "*branch tip is behind*"}))) {
    }

    $outp | write-host
}

The name of the remote respository is “github”.

This approach pushes each commit of the local repository to remote repo. It starts with the first commit.