Microsoft KB Archive/331451

= PRB: Copy Database Wizard Does Not Copy Server Roles When it Transfers Logins =

Article ID: 331451

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q331451



SYMPTOMS
When you use the Copy Database Wizard to copy or move a database to another server, the server roles associated with the login are not copied to the destination server.



WORKAROUND
To work around this problem, you can copy server roles by using any of the following methods:

Method 1

Use the Copy SQL Server Objects Task task of the Data Transformation Services (DTS) package to copy the server roles associated with the login.

To set the properties of the Copy SQL Server Objects Task task, in the Copy SQL Server Objects Task Properties dialog box, follow these steps:
 * 1) Click the Source tab. In the Source dialog box, type the name of the source database.
 * 2) Click the Destination tab. In the Destination dialog box, type the name of the destination database.
 * 3) Click the Copy tab. In the Copy dialog box, clear all the default options, and then click the Options button.
 * 4) Click to select the Copy SQL Server Logins (Windows and SQL Server Logins) check box.

Method 2

Follow these steps to transfer the server roles associated with the login:

  Execute the following Transact-SQL script in the source database. The output is the Transact-SQL script that will transfer the logins and roles to the destination database. declare @ServerRole nvarchar(35)

declare @MemberName sysname

declare srvrolemember cursor for select 'ServerRole' = spv.name, 'MemberName' = lgn.name from master.dbo.spt_values spv, master.dbo.sysxlogins lgn, sysusers u  where spv.low = 0 and spv.type = 'SRV' and lgn.srvid IS NULL and spv.number & lgn.xstatus = spv.number and lgn.sid = u.sid and lgn.name <> 'sa' order by 'MemberName' for read only

open srvrolemember fetch next from srvrolemember into @ServerRole, @MemberName while @@fetch_status = 0 begin Print 'exec sp_addsrvrolemember N + @MemberName + , ' + @ServerRole fetch next from srvrolemember into @ServerRole, @MemberName end close srvrolemember deallocate srvrolemember

Here is an example output:

exec sp_addsrvrolemember N'user1', securityadmin exec sp_addsrvrolemember N'user2', setupadmin exec sp_addsrvrolemember N'DOMAIN1\user3', sysadmin exec sp_addsrvrolemember N'DOMAIN1\user4', sysadmin  Copy or move the database by using the Copy Database Wizard. Execute the output of step 1 in the destination database.

Additional query words: CDW

Keywords: kbprb KB331451

-

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

© Microsoft Corporation. All rights reserved.