Unanswered: yet another ERROR 15023 user role already exists (user DBO)
PROD ARSystem db is owned by machinename\administrator
PROD logins in ARSystem db is 'ARAdmin' which maps to user 'dbo'
TEST ARSsystem db is owned by 'sa'
TEST logins in ARSystem db is empty but i want it to resemble PROD, so I need to map ARAdmin to dbo which unfortunately seems nearly an impossible mission ?!?!
I backed up (enterprisemanager) production db and restored it on TEST server (also using GUI of enterprisemanager.
When showing TEST users in enterprisemanager - it is an empty list.
When running on TEST : sp_change_users_login 'report' it returns 1 row :
'dbo' with usersid 0xC925775392F6D411B3A300508BD3122B.
This HEXno. in TEST db is the same as the ARAdmin user sid in PROD master..sysxlogins.
(sp_change_users_login on PROD returns no rows.)
On TEST I want to map user ARAdmin to user dbo (as it is on PROD), but it gives the infamous error 15023 : user or role already exists in database.
I understand that I am supposed to solve this with sp_change_users_login - BUT - the user/role 'dbo' is apparently a "forbidden value for the parameter" as you can see :
exec sp_grantdbaccess 'dbo'
exec sp_change_users_login 'update_one','dbo', 'ARAdmin'
Server: Msg 15007, Level 16, State 1, Procedure sp_grantdbaccess, Line 91
The login 'dbo' does not exist.
Server: Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 38
Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure.
I guess that i should try to get rid of the registered user dbo in the restored db, and then recreate it, and map it to the already existing login ARAdmin - but how in gods name do I sequence the actions
I believe I have tried everything now....
(including delete 'dbo' from sysusers table, which rendered the database absolutely unusable )
Please respond if any of you have experienced the same behaviour and solved it ??
but - what if the problemuser is named 'dbo' ?
I tried to delete this line in sysusers earlier, but it caused numerous other problems in this database, and I ended up re-restoring the database.
Is it OK to just delete the line in sysusers for the ARSystem database - even if user is named 'dbo' ?
There IS a user (sp_change_users_login) which is not mapped to a login, but as I said - it is named dbo... (which is a forbidden value in eg. sp_change_users_login that must be used to repair this "orphaned user"...)