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'
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.
ReplyDeleteThe 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.
ReplyDeleteI 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
Oops - didn't see that the < dbname > (less than/greater than) construct (without spaces), didn't survive the comment system.
ReplyDeleteTrying again with curly brackets instead of less/greater than:
ALTER AUTHORIZATION ON DATABASE::{dbname} TO sa
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.
ReplyDeleteThank you very much vee! This "ALTER AUTHORIZATION ON DATABASE:: TO sa" did the trick for me after several hours of googling.
ReplyDeleteI just followed your steps but the database of sid is view in (master..sysdatabases) might not exist in (master..syslogins)
ReplyDelete