Dectect and preventing error “Msg 33009”

If you work with SQL Server, you can potentially encounter the following error message:
Msg 33009, Level 16, State 2, Procedure usp_xxxxx, Line 0
The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘YourDatabase’. You should correct this situation by resetting the owner of database ‘YourDatabase’ using the ALTER AUTHORIZATION statement.
 
To get the owner SID recorded in the master database, run:
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()
To get the owner SID recorded in the current database, run:
USE YourDatabase
GO
SELECT sid FROM sys.database_principals WHERE name=N’dbo’
 
This issue is caused by restoring a database from a backup. The database owner of restored database is current user and is logged in the sys.database in master database. The original user of the database is dbo and in sys.database_principals in the  restored database, which has the same value before it’s backed. So if the user that restores the database is different than the owner of the backed up database, you will see that the SID returned by the two queries are different. You can fix this by changing the owner of the database:
ALTER AUTHORIZATION ON DATABASE::YourDatabase TO [Login]
 
The new owner "Login" should not already be a user in YourDatabase. You can always safely change the database owner to "sa" because sa cannot be added as a user to any database. However, some server may do not have login "sa" because in SQL Server 2005, it’s allowed to change to the login name "sa". What cannot be changed is the SID of sa, it’s always 0x01. We can get the login name by the SID 0x01 and change the database owner to it:
 
DECLARE @Login sysname
select @Login =SUSER_SNAME(0x01)
DECLARE @SQL nvarchar(1000)
SET @SQL=N’
ALTER AUTHORIZATION ON DATABASE::’+DB_NAME()+N’TO [‘+@Login+N’]’
GO
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

7 Responses to Dectect and preventing error “Msg 33009”

  1. Unknown says:

    This problem can also occur with the creation of new databases, if the new owner "Login" is a user in the model database.
     
    Thanks, Peter, for helping me solve a perplexing problem!

  2. Robert says:

    Yes I have also seen this problem when the database has been restored from backup plus when running the SQL you show the owner ID from sys.databases does match the owner sid of the Db giving the error and the ALTER AUTHORIZATION statement fixes the problem nicely.

  3. boxing says:

    This information was very useful to me in troubleshooting a failed upgrade from SQL 2000 SP4 to SQL 2008 SP1. I appear to have had this kind of mismatched ownership on msdb. Though nobody is certain how it happened. We\’ve never restored it.I was pretty suprised at the way in which it completely broke the upgrade process. The first i knew of it was when the SQL setup failed with this error:"Message: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes..", leaving the SQL Server Services unable to startThe application event log didnt give me much of a clue with 2 errors from MSSQQLSERVER, but thankfully they were enough to bring me to this blog.ID: 912Script 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.and ID: 3417Cannot 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.I eventually found what was really going on by running SQLSERVR.EXE -m from the command line. This showed me what was going on during service startup. I could see several upgrade scripts running. The service was then terminating with Error: 33009, Severity: 16, State: 2.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.Error: 912, Severity: 21, State: 2.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.2009-06-13 13:37:21.32 spid6s Error: 3417, Severity: 21, State: 3.2009-06-13 13:37:21.32 spid6s 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.SQL Trace was stopped due to server shutdown. Trace ID = \’1\’. This is an informational message only; no user action is required.Panic was suddently replaced by hopeTo fix the issue, I used the same SQL service startup trace flags as referenced in MS KB article 960781 (how to recover from a failed upgrade due to a renamed SA account). This gave me a way to start the service without the upgrade scripts running against the DB during startup, and breaking it. I was then able to login using the admin-only connection (http://msdn.microsoft.com/en-us/library/ms178068.aspx)Once connected to the SQL again the logic of your excellent explaination of this issue and T-SQL snippets helped me to confirm the problem and fix it by returning the ownership of msdb to SA. The upgrade scipts were then able to run as normal against the DB when the service was restarted and everything was back to normal.I ran a repair install, just to be safe, but everythings ok now.You know it might be a good idea to put some logic in the upgrade advisor for that. I somehow dont think i\’m the only one who\’s going to come unstuck by it.Apologies for the long post. I just want to increase the chances of a google hit for someone who might get the same problem, sometime, and save them a heart attack, or at least stop them from following the error messgaes advice to restore or rebuild the master DB, or stop them performing a full DR recovery on their SQL server (as I was thinking about doing) before realising this is actually quite an easy fix….cheers again for the info – you saved my weeked 🙂

  4. Grigoriy says:

    Thank you for post!I started my quest with no working for no reason sp_send_dbmail and ended up here to get all dots together!Path was ALTER DATABASE SET TRUSTWORTHY ON and then ALTER AUTHORIZATION ON DATABASE Your post explained why production Db works and Dev not even though I added notthat much yet.Thanks again!

  5. AlCaponi says:

    Thank you very much!
    Saved the rest of my day. I’ve had the same Problem as “boxing”.
    After a in-place upgrade from SQL2005 to SQL2008R2 the services won’t start because the dbo of msdb wasn’t the “sa” user.
    With boxing post and yours i was able to resolve the issue.
    Greatings from Germany

  6. Bogdan B says:

    Peter / Boxing – I owe you a bear, thanks!

  7. Arun says:

    This blog Really helped us Thanks a lot 🙂

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