Sql Server 2008 (and 2005):
I have a Sql Server instance, let's say, InstA, in a Domain (called AD). In that I have a domain Windows Authentication Login, called AD\Log1 that has an associated user, User1 in a database called DB1.
So, now I backup this database and restore on a new instance (Inst2) that is not part of any domain. It has an existing login (LocalMachine\Log2) that I need to have mapped to the existing User1 in the newly restored database DB1.
With sp_change_users_login, I get the following error:
EXEC sp_change_users_login 'Update_One', 'User1', 'LocalMachine\Log2'
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'User1' is absent or invalid.
If I use
ALTER USER User1 WITH LOGIN = 'LocalMachine\Log2', it renames the user User1 to LocalMachine\Log2 and maps it to LocalMachine\Log2 login. It works, but I do not want it to rename.
I would prefer to map this existing database user User1 in the database DB1 to an existing login in the new instance inst2 without changing the user name or creating a new login (
AutoFix). Just update the SID and map it (without other changes).
Is it possible?
What I gathered so far is, for my situation,
sp_change_users_login does not work because LocalMachine\Log1 is a Windows Account (not a Sql Login). but if both were Sql Logins,
sp_change_users_login with work perfectly.