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”.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.