Duplicate Users
-
Well here is the scenario
I restored a database from server A to Server B. this database on the server A had a sql user called 'moon'. Well after I restored it on Server B I found out that SQL Server on Server B already has a sql user called 'moon'. so now I am stuck. I am unable to assign 'moon' on Server B access to this database that I just moved as the SID of user 'moon' on Server A is different from the one on Server B.
If I try to give user 'moon' access to this restored db I get following error message:
Error 21002: [SQL-DMO}User'Moon' already exists.
How can resolve this issue? thanks
-
and another piece of info... user 'moon' which already exists on Server B is mapped to another database. so I cannot remove it.
-
Use the obscure and hard-to-find-out-about sp_change_users_login procedure. Check in BOL for details; here's the template I use when I need it (which is often):
-- Check over scrambled SIDs
EXECUTE sp_change_users_login 'report'
EXECUTE sp_change_users_login 'auto_fix', 'moon'
Philip
-
There are also scripts on this site (and most SQL sites) for fixing the "orphaned users" that you describe. I would guess they they use the procedures specified above; but they are probably a bit more automated.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
sp_change_user_login will surely help you
Pankaj Khanna
Database Administrator - SQL Server 2000Keep hope to keep you intact...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply