How To: Restore a SQL Server Database with Windows Logins to Another Machine

Summary

Instructions provided describe how to restore a SQL Server database with Windows logins to another machine. When users backup or detach to send a database for testing, restore/reattach is used to restore the database.However there are some cases when it is necessary to re-synch Windows logins that were created on different domains or are local to different machines.

Note:
The following procedures only apply to SQL Server 2005, 2008 and 2012.

• For SQL Server 2000, refer to the following article: How to transfer logins and passwords between instances of SQL Server.
• See the following Esri Knowledge Base article to re-synch SQL Server logins: Re-synch SQL Server logins or users after restoring a database from backup - SQL Server 2000/2005.

Procedure

Assume the following actions are to take place:

• Re-synch Windows login, udm\gis (user name is udm\gis in the database).
• Restore the database to the local machine, testm.

There are three steps for the above actions to take place:

• Create a local Windows account.
• Create a login in SQL Server for this Windows account.
• Remap the user name from the old login to the new Windows login.

Create a Windows account (for remapping user's Windows logins) on the machine where database is being restored. For this example, use testm\gis.
Add the testm\gis Windows login to SQL Server.
After restoring the user's database, remap the udm\gis user to the testm\gis login:

In the database, execute this statement:
```
ALTER USER [udm\gis] WITH LOGIN = [testdm\gis]
```

Article ID:000010203

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Related Information

Re-synch SQL Server logins or users after restoring a database from backup

Restore a SQL Server database with Windows logins to another machine

Summary

Procedure

Related Information

Discover more on this topic