PRB: Copy Database Wizard Does Not Copy Server Roles When it Transfers Logins (331451)

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:

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

2. Copy or move the database by using the Copy Database Wizard.
3. Execute the output of step 1 in the destination database.