Fix SQL Database SIDs After Moving Database

After moving my GoldMine databases to a new SQL server, I was getting thousands of these events in the Application event log as well as in the SQL ERRORLOG:

Log Name:      Application
Source:        MSSQLSERVER
Date:          7/29/2017 3:20:03 PM
Event ID:      9724
Level:         Information
Description:
The activated proc ‘[dbo].[SqlQueryNotificationStoredProcedure-817cf99e-e144-4730-b3e9-f0918a297507]’ running on queue ‘GoldMine_Test.dbo.SqlQueryNotificationService-817cf99e-e144-4730-b3e9-f0918a297507’ output the following:  ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’

I also got some error events that sound similar:

Log Name:      Application
Source:        MSSQLSERVER
Date:          7/29/2017 9:53:54 PM
Event ID:      28005
Description:
An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.

I found this DBA Exchange answer most helpful. In SQL Server Management Studio, the SIDs in these commands should match:

SELECT name, owner_sid FROM sys.databases where name = 'GoldMine_Test';
SELECT sid, name from GoldMine_Test.sys.database_principals WHERE name = 'dbo'
By creating a test database from GoldMine with SQL user “goldmine”, I found out its SID. I also confirmed that the “goldmine” user was not listed under that database’s Security > Users, but it is shown as the login for the dbo user:

Solution

This solution assumes you have a firm grasp of SQL management. I’m showing examples only; you’ll need to modify this for your environment. Use at your own risk.

1. Under the database’s Security > Users, delete the ‘’goldmine” user, if present. This will allow you to assign the “goldmine” user in the next step, which may have the same name but a different SID if you moved the database to a new server.

2. Open the properties of the database. On the File pane, change the Owner to “goldmine” (or whatever SQL user should own your GoldMine databases).

Alternatively, you can use a SQL command like this:

ALTER AUTHORIZATION ON DATABASE::GoldMine_Test TO [goldmine];

3. Re-run the above SQL commands to make sure that the SIDs now match.