Copy Database Wizard - Create User before Login

Question

Copy Database Wizard - Create User before Login

mattyk6

Ten Centuries

Points: 1152
More actions
May 17, 2006 at 4:47 pm

#114239

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Manix Say Hey Kid Points: 685 More actions · Answer 1

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..

mattyk6 Ten Centuries Points: 1152 More actions · Answer 2

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)

Beginning of package execution.

Maximum concurrent executables are set to 1.

(null)

Task just started the execution.

Connecting to server SOURCE

Connected to server SOURCE

Connecting to server DESTINATION

Connected to server DESTINATION

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 log file DB_Log in path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DB_log.LDF

CREATE DATABASE [DB] ON PRIMARY

Created the database DB in destination DESTINATION

Transferring data to database DB from DB

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 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\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. */

Transferred login matthew to destination

Login transfer is complete.

Transfer objects finished execution.

(null)

The Execution method succeeded

(null)

End of package execution.

Nicola Harding SSC Enthusiast Points: 102 More actions · Answer 3

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

mattyk6 Ten Centuries Points: 1152 More actions · Answer 4

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.

TKE402 Old Hand Points: 344 More actions · Answer 5

Did you get this to work? I am having the same error.

Thanks,

TKE402

mstjean Hall of Fame Points: 3310 More actions · Answer 6

> 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.

Cursors are useful if you don't know SQL