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,

UB01
  • 937
  • 2
  • 9
  • 18
  • 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 '12 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 '12 at 18:32
  • Did you try also specifying WITH NAME = User2? – Jon Seigel Jun 22 '12 at 18:45
  • From what I read from BOL: WITH NAME = User2 renames user1 to user2. – UB01 Jun 22 '12 at 18:50
  • @JonSeigel I do not want to rename after mapping User1 to Log2. It's not about avoiding the extra step, I am trying to understand (1) why it does not work and (2) why does it rename? – UB01 Jun 22 '12 at 18:52
  • Just a second ago, I edited my Original Post with more info about Login: Log2 (supposed to be LocalMachine\Log2 -- Local machine Windows Account) – UB01 Jun 22 '12 at 18:56
  • Sorry, typo. I meant User1 (i.e., the existing user name). I have no idea why it renames automatically. – Jon Seigel Jun 22 '12 at 19:06
  • 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 '12 at 19:38
  • @kozloski Thank you, that was a good observation. The whole time, it was right in front of my face, but I did not pay attention. Thank you. – UB01 Jun 22 '12 at 20:38

1 Answers1

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.

Brent Ozar
  • 42,952
  • 47
  • 220
  • 375