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:

SQL SID 1

SQL SID 2

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

SQL SID 3

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.

Leave a Reply

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

Notify me of followup comments via e-mail. You can also subscribe without commenting.

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