PRB: The Copy Database Wizard Does Not Copy the Default Database Information for Logins (331450)

WORKAROUND

1. Run the following Transact-SQL script in the source database:

   declare @DefaultDB nvarchar(35)
   declare @MemberName sysname
   
   declare cursDefaultDB cursor for
          select 'DefaultDB' = sd.name, 'MemberName' = lgn.name
          from
                   master.dbo.sysxlogins lgn,
                   sysusers u,
                   master..sysdatabases sd
          where
                   lgn.srvid IS NULL and
                   lgn.sid = u.sid and
                   lgn.dbid = sd.dbid and
                   lgn.dbid = db_id()
         order by 'MemberName'
   for read only
   
   open cursDefaultDB
   fetch next from cursDefaultDB into @DefaultDB, @MemberName
   while @@fetch_status = 0
       begin
          Print 'exec sp_defaultdb N''' + @MemberName + ''', ' + @DefaultDB
          fetch next from cursDefaultDB into @DefaultDB, @MemberName
       end
   close cursDefaultDB
   deallocate cursDefaultDB
   				

   The output is the Transact-SQL script that sets the default database for the copied logins in the destination database server.
   
   Sample output:
   

   exec sp_defaultdb N'user1', pubs
   exec sp_defaultdb N'DOMAIN1\user2', pubs
   exec sp_defaultdb N'DOMAIN1\user3', pubs
   				

2. Use the Copy Database Wizard to copy or move the database.
3. Run the output of step 1 in the destination database.