Login User Mapping issue in SQL Server 2008

Question

A while back I set up a database under SQL Server 2008 called myDB in Windows XP, then under Logins under the server, I clicked Properties on my computer login name COMP23/Andrew and mapped myDB database to this using dbowner as its rights.

Then I cloned this XP installation as a backup, installed Visa, realising I did not want Vista I re-imaged back my original XP copy onto the same machine. However the DB mapping has got really confused! Basically under the server login COMP23\Andrew, it says its mapped to myDB, but when I click myDB and look at its users its not there. I think its lost its SID mapping because it thinks its a new machine.

Under the server login COMP23\Andrew I can't untick the mapping to myDB as when I do it says "Cannot drop the user dbo". I can't alter the dbo user either - it won't let me. But nor can I make the user appear under myDB users! Which means I can't login through my website settings (asp.net web.config) file! When I login it just says Cannot open database "myDB" requested by the login. The login failed. Login failed for user 'COMP23\ASPNET'

Any ideas? How I can remap this properly? I've even tried reinstalling SQL Server 2008 but the computer name is still there mapped to the database.

Answer 1

Because dbo is the owner of the database, its mapping must e changed by changing the owner of the database:

ALTER AUTHORIZATION ON database::[<yourdb>] TO [sa];

Answer 2

Since you mentioned the SID mapping issue, have you tried using sp_change_users_login? Use the autofix option to re-map your login to the one in the database.

For your example above you should execute the following while connected to the database

EXEC `sp_change_users_login` @Action = 'autofix', @LoginName = 'COMP23\ASPNET'