The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘msdb’. You should correct this situation by resetting the owner of database ‘msdb’ using the ALTER AUTHORIZATION statement.

-> We were working on an upgrade project. We were performing an upgrade of a standalone SQL server 2005 to SQL server 2012. The upgrade failed with the error “The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘msdb’. You should correct this situation by resetting the owner of database ‘msdb’ using the ALTER AUTHORIZATION statement.”.

-> The SQL server version changed to SQL server 2012. We tried a repair and it failed.

-> We tried starting the SQL services and got the below error in the errorlog.

.
.
2016-01-05 20:35:00.25 spid5s      Error: 33009, Severity: 16, State: 2.
2016-01-05 20:35:00.25 spid5s      The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘msdb’. You should correct this situation by resetting the owner of database ‘msdb’ using the ALTER AUTHORIZATION statement.
2016-01-05 20:35:00.25 spid5s      The failed batch of t-sql statements :
2016-01-05 20:35:00.25 spid5s      Error: 912, Severity: 21, State: 2.
2016-01-05 20:35:00.25 spid5s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 33009, state 2, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2016-01-05 20:35:00.26 spid5s      Error: 3417, Severity: 21, State: 3.
2016-01-05 20:35:00.26 spid5s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2016-01-05 20:35:00.26 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

-> I added trace flag -T902 in the SQL server startup parameter and started the SQL services to see if it starts, it started without any issues.

902 Bypasses execution of database upgrade script when installing a Cumulative Update or Service Pack.

-> Went into SQL server management studio and ran the below query. “select * from sys.databases”.

-> The owner for master database was SA -> sid: 0x01. But MSDB was something else. Executed the below command and changed the owner of MSDB to SA.

use msdb
go
sp_changeobjectowner ‘sa’
go

-> Once this was done, I removed the trace flag -T902 from the SQL server startup parameter and started the SQL services and it started fine without any issues.

->  Please note that you cannot change the owner of system databases (Master, Model and tempdb) as per link http://technet.microsoft.com/en-us/library/ms190909(v=SQL.105).aspx.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s