2

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.

Thanks,

9
  • How do you expect it to authenticate if its login is associated with a Windows principal that can't be verified since Inst2 is not on the domain??
    – swasheck
    Jun 22, 2012 at 16:53
  • I am not sure if I understand your comment. But Inst2 has its own login: Log2. I want Log2's SID to be mapped to the DB user User1. Does it help?
    – UB01
    Jun 22, 2012 at 18:32
  • Did you try also specifying WITH NAME = User2?
    – Jon Seigel
    Jun 22, 2012 at 18:45
  • From what I read from BOL: WITH NAME = User2 renames user1 to user2.
    – UB01
    Jun 22, 2012 at 18:50
  • 1
    The BOL article linked to by @UB01 explains the behavior: "The name of the user will be automatically renamed to the login name if the following conditions are true. The user is a Windows user. The name is a Windows name (contains a backslash). No new name was specified. The current name differs from the login name." I would think the name of the database user wouldn't really be material, since it should retain the same database_principal_id and permissions.
    – kozloski
    Jun 22, 2012 at 19:38

1 Answer 1

1

When you run sp_change_users_login, it automatically renames users as explained in Books Online:

The name of the user will be automatically renamed to the login name if the following conditions are true. The user is a Windows user. The name is a Windows name (contains a backslash). No new name was specified. The current name differs from the login name.

To keep life easy, I'd keep the same usernames on both servers and then you should be fine with sp_change_users_login.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.