Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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,

share|improve this question
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
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
show 4 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.