No dbo user after RESTORE

[TonyKhela]

After restoring a DB, I find that I no longer have a dbo user. This is causing lots of issues eg. I cannot change the sa password, because it gives an error stating that dbo user does not exist.

I think its because the login which mapped to the dbo in that original Db does not exist in the new Server. So the restore process could not create this user.

Anyone come across this before?
If so how do you resolve this.
thx
TK

 

[TonyKhela]

I am answering my own query , but I've found the answer, and here it is in case it helps someone else.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;305711

CAUSE
Enterprise Manager is incorrectly filtering out all the users that do not have matching logins, and the dbo user does not have a matching login. Two possible reasons for this behavior to occur are:
If a database is created by a Microsoft Windows NT authenticated login that is granted access to the computer that is running SQL Server through group membership (such as BUILTIN\Administrators), the security identification number (SID) stored in the sysusers system table in that database does not have a matching SID in the syslogins system table.


If a database is restored where the dbo user's SID in the sysusers system table is not matched with the SID in the syslogins system table.
WORKAROUND
To work around this behavior, use either of these methods:
If the dbo user does not have an explicit login, change the owner of the database to a user that has an explicit login. For example, change the owner of the database to the system administrator (sa), and then execute the following code:exec sp_changedbowner 'sa'

If the dbo user does have an explicit login but that user's SID in the sysusers system table does not match the login's SID in the syslogins system table (possibly due to a database restore) use the sp_change_users_login stored procedure to restore the SID mapping.