May 17, 2006 at 4:47 pm
I have a weird oddity that I can't figure out. This is dealing with the Copy Database Wizard and copying a database and logins from one 2005 server to another 2005 server.
I have a 2005 Database Server with mixed mode authentication, some window users and groups and some sql logins. Whenever I try to copy a database (and logins) that have sql logins, I get an error saying that it cannot create the user. When I try to copy a database (and logins) that only has window logins, it copies just fine. If I create the sql login on the new server and then do the copy, no error.
When looking at the log file, it appears that it is trying to create the users before creating the logins. The user that I am using is a sysadmin on both servers. Also, on the source server, 'user' has a default schema of dbo and so there is no schema of 'user' in the database.
Why is it doing this?
Thanks,
Matt
BTW, here is the error msg from the Log file:
errorCode=-1073548784 description=Executing the query "CREATE USER FOR LOGIN WITH DEFAULT_SCHEMA=
" failed with the following error: "'user' is not a valid login or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
May 18, 2006 at 8:06 am
When you are copying from one sql2k5 server to another then the Copy Database Wizard should include another screen that allows you to specify 'Additional database object to transfer'. Sounds like maybe the Login object is not selected in that window? If it is then maybe you found a bug..
May 18, 2006 at 8:57 am
The login objects is selected.
The order in the log appears to be like this:
copy database
attach database
create user
create login
But the weird thing is that this works if it is a Windows login, but not for a SQL login.
Here is the messages column from the process:
message
(null)
(null)
(null)
(null)
Beginning of package execution.
Maximum concurrent executables are set to 1.
(null)
(null)
(null)
(null)
Task just started the execution.
Connecting to server SOURCE
Connecting to server SOURCE
Connected to server SOURCE
Connected to server SOURCE
Connecting to server DESTINATION
Connecting to server DESTINATION
Connected to server DESTINATION
Connected to server DESTINATION
Transferring database DB from SOURCE server as DB to DESTINATION server
Transferring database DB from SOURCE server as DB to DESTINATION server
Added data file DB_Data in path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DB_Data.MDF to file group [PRIMARY]
Added data file DB_Data in path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DB_Data.MDF to file group [PRIMARY]
Added log file DB_Log in path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DB_log.LDF
Added log file DB_Log in path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DB_log.LDF
CREATE DATABASE [DB] ON PRIMARY
CREATE DATABASE [DB] ON PRIMARY
Created the database DB in destination DESTINATION
Created the database DB in destination DESTINATION
Transferring data to database DB from DB
Transferring data to database DB from DB
ERROR : errorCode=-1073548784 description=Executing the query "CREATE USER [matthew] FOR LOGIN [matthew] WITH DEFAULT_SCHEMA=[dbo]
ERROR : errorCode=-1073548784 description=Executing the query "CREATE USER [matthew] FOR LOGIN [matthew] WITH DEFAULT_SCHEMA=[dbo]
Database transfer is complete.
Login BUILTIN\Administrators already exists in the destination server and so will not be transferred.
Login BUILTIN\Administrators already exists in the destination server and so will not be transferred.
Login cruser already exists in the destination server and so will not be transferred.
Login cruser already exists in the destination server and so will not be transferred.
Login DOMAIN\User1 already exists in the destination server and so will not be transferred.
Login DOMAIN\User1 already exists in the destination server and so will not be transferred.
Login DOMAIN\User2 already exists in the destination server and so will not be transferred.
Login DOMAIN\User2 already exists in the destination server and so will not be transferred.
Transferring login matthew
/* For security reasons the login is created disabled and with a random password. */
/* For security reasons the login is created disabled and with a random password. */
Transferred login matthew to destination
Transferred login matthew to destination
Login transfer is complete.
Login transfer is complete.
Transfer objects finished execution.
(null)
The Execution method succeeded
(null)
End of package execution.
September 27, 2006 at 9:35 am
Hi There, I read somewhere online that if you run the copy db wizard a subsequent, 2nd time then it all works fine because the user then exists... if this works for you, could you confirm this please? G.Luck
September 27, 2006 at 9:44 am
Of course it would work the second time.
The issue is that the first time you run this process it tries to create the user and then the login. The Copy Database Process needs to create the login and then the user.
If you run it a second time, it would suceed because the login was created the first time it was run.
BTW, I have not tested this in the last 5 months.
August 5, 2009 at 7:01 am
Did you get this to work? I am having the same error.
Thanks,
TKE402
August 6, 2009 at 10:52 am
> But the weird thing is that this works if it is a Windows login, but not for a SQL login.
Off the top of my head I'd say THIS is the reason.
Windows authenticated login/user on sql server 1 *IS* the same windows authenticated account as the same-named one on sql server 2.
SQL authenticated login/user on sql server 1 *IS NOT* the same account as the same-named one on sql server 2.
What I do when I copy a db from server to server (the first time) is to create the sql login on the target server if it does not already exist. THEN, I drop and re create the sql user in the db.
If your same-named sql account has a different password on different server you may confuse people further.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply