How to add the farm account to the Sync DB users as DBO with a default schema of DBO

Question

In my SharePoint 2013 environment, the UPS sync service has stopped working and I found out from the ULS logs that and then checked it the database, the farm account when checked under user mapping for the sync database does not show "dbo" under the users tab, but shows "dbo under the default schema whereas it should be showing as dbo in both the columns like all other services.

How would I go about and fix this?

This is the existing situation

I want to get here

Answer 1

In your database, one of your accounts (SPInstall, SPSetup, SPService...) is holding the dbo user, with the default schema dbo. Like the following table:

Database    User   Default Schema
========    ====   ==============
Sync_DB     dbo    dbo

Now, there can only be one account holding the dbo user in SQL Server, and you need to find out which user that is. When you do, set that account in the user column as in your list in your question. Like this:

Database    User     Default Schema
========    ====     ==============
Sync_DB     Account  dbo

Then assign the SPFarm account the dbo user with default schema dbo, and you are good to go.

Answer 2

Try an Unprovision/Provision of the SyncDB by running the following script on a SharePoint Server:

Add-PSSnapin *share*

$syncDB =  Get-SPDatabase | ? {$_.Displayname -like "[Your Sync DB]"}

$syncDB.Unprovision()
$syncDB.Status = "Offline"

$upsa = Get-SPServiceApplication | ? {$_.TypeName -like "*User Profile Service Application*"}
$upsa.ResetSynchronizationMachine()
$upsa.ResetSynchronizationDatabase()

$syncDB.Provision()
$syncDB.status

You will need to grant your farm account permissions on the db again. Afterwards the you should be able to start the Profile Sync Service.

Answer 3

I deleted the SharePoint UPS Service application, along with the Sync database in the database server.

The Social and Profile database stores all the User profile specific data so the Sync database can be deleted and recreated, it doesn't affect the application.

Then I created the UPS service application again which created the sync database with appropriate permissions. Problem Solved.