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)
ReplyDeleteIt is very good blog and useful for students and developer ,
ReplyDeleteSql server DBA Online Course Bangalore