Tuesday, March 29, 2011

The database owner SID recorded in the master database differs from the database owner SID recorded in database

The database owner SID recorded in the master database differs from the database owner SID recorded in database '<Database>'. You should correct this situation by resetting the owner of database '<Database>' using the ALTER AUTHORIZATION statement.

This problem can arise when a database restored from a backup and the SID of the database owner does not match the owners SID listed in the master database.

To correct this issue, run the following command:

Alter Authorization on Database::<Database> to [<USER>]

To check who is the owner listed in the master database run the following:

SELECT  SD.[SID]
       ,SL.Name as [LoginName]
  FROM  master..sysdatabases SD inner join master..syslogins SL
    on  SD.SID = SL.SID
 Where  SD.Name = '<Database>'

To check what SID is the DBO in the restored database run the following:

Select [SID]
  From <Database>.sys.database_principals
 Where Name = 'DBO'

10 comments:

  1. This was a while ago I know, but I've just had the same issue and was able to sort it with this. Thank you.

    ReplyDelete
  2. The owner_sid in the sys.databases view (master..sysdatabases) might not exist in the sys.syslogins view (master..syslogins) - in which case your first SELECT will be empty.

    I used the following modification to see which databases were without a valid owner:

    SELECT SD.owner_sid
    ,SL.Name as [LoginName]
    ,SD.name as dbname
    FROM sys.databases SD left join sys.syslogins SL
    on SD.owner_sid = SL.sid
    WHERE SL.name IS NULL

    - and the following to fix it:

    ALTER AUTHORIZATION ON DATABASE:: TO sa

    ReplyDelete
  3. Oops - didn't see that the < dbname > (less than/greater than) construct (without spaces), didn't survive the comment system.

    Trying again with curly brackets instead of less/greater than:

    ALTER AUTHORIZATION ON DATABASE::{dbname} TO sa

    ReplyDelete
  4. Thanks Jens, Someone did a restore of a database and ran into that error message. I did: "ALTER AUTHORIZATION ON DATABASE:: TO sa" and was able to get pass my error.

    ReplyDelete
  5. Thank you very much vee! This "ALTER AUTHORIZATION ON DATABASE:: TO sa" did the trick for me after several hours of googling.

    ReplyDelete
  6. I just followed your steps but the database of sid is view in (master..sysdatabases) might not exist in (master..syslogins)

    ReplyDelete