Copy database fails while trying to copy login I'm trying to copy a database from a server that lives in a unique domain, call it AMCO.

The destination server, which is on domain BCO, is running sql server agent, and as far as I can tell everything is set up properly there.

When I use the copy database feature of management studio, I can set everything up just fine. During the actual job execution however, this appears in the log:


  Started:  5:27:19 PM  Progress: 2012-07-31 17:27:20.04     Source: 
  192_168_50_11_SQLSERVER2008_localhost_SQLSERVER2008_Transfer Objects Task      Task just started the execution.: 0% complete  End Progress  Error: 2012-07-31 17:27:27.34     Code: 0x00000000     Source: 192_168_50_11_SQLSERVER2008_localhost_SQLSERVER2008_Transfer Objects Task      Description: ERROR : errorCode=-1073548784 description=Executing the query "CREATE LOGIN [AMCO\AMCOAdmin] FROM WINDOWS WITH DEFA..." failed with the following error: "Windows NT user or group 'AMCO\AMCOAdmin' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.   helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}


Why does this user need to be copied first? I am removing the option of "copy logins" from the additional data to copy, and yet this error still comes up.
Do yourself a favor and stay away from the copy database functionality of SSMS.  Just do a full database backup and then restore on the instance you are looking for.  You can fix orphaned users on the destination instance through the use of sp_change_users_login.

On your Source Instance:

backup database YourDatabase
to disk = 'C:\YourBackupDir\YourDatabase.bak'
go


On your Destination Instance:

restore database YourDatabase
from disk = 'C:\YourBackupDir\YourDatabase.bak'
go


You may have to move that backup onto a share or port it to the destination server, but you get the idea.