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