At my site we've generally used SQL Server authentication, but now we have
two AD domains (production and test) and are starting to use Windows
authentication. We have a production database (on a server in the production
domain) with a user account that uses Windows authentication. Here is an
excerpt from the sp_helpuser output in the production database:

UserName LoginName
--------------------- --------------------
MNDOC\BizTalkService MNDOC\BizTalkService

I've been asked to restore a backup of this database to a development server
in our test domain. The same SQL Server logins exist on both servers, so
after the restore, I used sp_change_users_login to remap the SQL Server
authenticated user accounts to SQL Server logins on the dev. server.
However, the Windows-authenticated account is orphaned as shown by
sp_helpuser:

UserName LoginName
--------------------- ---------
MNDOC\BizTalkService NULL

In our test domain there is an account MNDOCTEST\BizTalkService, so I ran
the following: CREATE LOGIN [MNDOCTEST\BizTalkService] FROM WINDOWS

Is there a way to map the database user MNDOC\BizTalkService to the login
MNDOCTEST\BizTalkService on our development server, in other words a way to
map user to login along the lines of sp_change_users_login, but for Windows
logins?

Sorry that my understanding of domains and domain jargon is so limited, but
there is no "trust" (if that's the correct term between MNDOCTEST and MNDOC
domains, so on the development server if I try to run the following: CREATE
LOGIN [MNDOC\BizTalkService] FROM WINDOWS

this message is returned:
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'MNDOC\BizTalkService' not found. Check the name
again.

And I think that is the way our network people want it to be, so that is why
I was wondering if the user account can be mapped to a login in different
domain after the database is restored.

(By the way, if we continue to try to have our production and development
environments in different domains, I can see that it will be preferable to
not have a domain name in the username.)

I am not aware of any option similar to sp_change_users_login that is
specific to Windows logins. The concept behind that is the existence of a
different SID value that pertains to the same SQL Server login due to the
backup/restore method. Since your two domains do not have a trust
relationships defined, the best way to approach this is to drop the existing
login - MNDOC\BizTalkService - and add the new login from the test domain

"Mark_E" <Mark_E (AT) news (DOT) postalias> wrote