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