I ran across the error “the login already has an account under a different username” while trying to copy a database from one server to another using SQL’s copy database tool. This error occurred using both the SQL 2012 and SQL 2016 versions of Microsoft SQL Management Studio. Scroll to the end if you want to see the full error from the event logs.
I found this issue is related to the account already existing on the destination server AND having ownership of an existing database. This typically occurs due to using an AD account to install software that installs a database, making that AD account dbo of the database instead of sa.
You can check to see if this is your issue by running this command on each of your databases:Use YourDBName
Select l.name as LoginName, u.name as UserName
from sys.server_principals l
join sys.database_principals u on l.sid = u.sid
Look for any databases where the LoginName is not sa and the UserName is dbo.
If you want to run it on all your databases at one time, you can utilize the sp_MSforeachdb stored procedure.execute master.sys.sp_MSforeachdb
'use [?]
Select l.name as LoginName, u.name as UserName
from sys.server_principals l
join sys.database_principals u on l.sid = u.sid'
SQL dbo List
There are two ways to workaround this issue. The first workaround is my recommendation. Use the Backup/Restore functionality instead of the Copy Database functionality. The second workaround I do not recommend unless you know 100% that it will not break your application. Change the dbo from the user’s account to the sa account.
Here is a quick summary:
1) Backup from source server and Restore to destination server instead of using the Copy Database tool. (Recommended)
2) Change dbo to sa. (Not recommended due to risk of applications breaking)
If you know changing the dbo to sa will not affect your applications, you can do it with this command:execute YourDBName.[dbo].[sp_changedbowner] @loginame = 'sa', @map = 'true'
Event Log
Level: Error
Source: SQLISPackage110
Event ID: 12550 Event Name: OnError
Message: An error occurred while transferring data. See the inner exception for details.
StackTrace: at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
InnerException-->The login already has an account under a different user name.
StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Smo.Transfer.ExecuteStatements(SqlConnection destinationConnection, IEnumerable`1 statements, SqlTransaction transaction)
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
Operator: ADDOMAIN\aduser
Source Name: CDW_SOURCESQLSERVER_DESTINATIONSQLSERVER_9
Source ID: {4F0D4AD3-30DE-476F-9777-B4E9F6524BB7}
Execution ID: {5B530D2A-7878-43A8-B473-89CEEA584F8D}
Start Time: 11/18/2016 9:00:40 AM
End Time: 11/18/2016 9:00:40 AM
Data Code: 0
Glad it helped you. This was a rough one to figure out.
Thanks. Saved me a ton of time.