Microsoft KB Archive/331450

= PRB: The Copy Database Wizard Does Not Copy the Default Database Information for Logins =

Article ID: 331450

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q331450



SYMPTOMS
When you use the Copy Database Wizard to copy a database to another server, the default database information that is associated with the login (or logins) is not copied to the destination database server. And the default database of the login is set to the master database in the destination server.



WORKAROUND
To work around this behavior, copy the default database information that is associated with the logins in the destination database server:   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  Use the Copy Database Wizard to copy or move the database. Run the output of step 1 in the destination database.



STATUS
This behavior is by design.

Additional query words: CDW

Keywords: kbprb KB331450

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.