Copy Task fails if user created Roles have members - by Phlegmer

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 776161 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 1/9/2013 12:16:07 PM
Access Restriction Public


We have a production server and a test server.  Every so often we would like to update the test server db's to refresh the data from production.  Both SQL server versions are 10.50.1600 and I'm an admin on both.  Within SSMS, I'm trying to do a copy task from prod to test.  I have gotten this to work with other db's.  There is one in particular that has a custom role called 'db_executor'.  When I commit the copy task, it always fails with the following:

Message: ERROR : errorCode=-1073548784 description=Executing the query "sys.sp_addrolemember @rolename = N'db_executor', @..." failed with the following error: "The role 'db_executor' does not exist in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

A generous person on the MS forums was able to duplicate the issue and discovered 2 work arounds.  One is to use the db detach instead of SMO, the other is to remove all the members of the user created role.  Either of these will eliminate the error.
Sign in to post a comment.
Posted by Dmitry Shatilov on 11/20/2014 at 5:43 AM
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) - I can reproduce this issue
Posted by Dmitry Shatilov on 11/18/2014 at 1:56 AM
MS SQL Server 2008R2 x64 SP3 RUS - I can reproduce this issue
Posted by Konstantin Esaulov on 7/21/2014 at 9:13 PM

I can reproduce this issue on Microsoft SQL Server Standard Edition (64-bit) 2008 R2 10.50.4000.0 (SP2).
Posted by Charles [MSFT] on 4/5/2013 at 8:40 AM
Hello Phlegmer,

My apologies, SP2 is indeed the latest release for 2008R2.

-Charles Gagnon (
Posted by Phlegmer on 4/4/2013 at 8:22 AM
Thank you very much for the response. I can't seem to locate where to find SP3 for SQL 2008 R2. According to this link,, the latest is SP2.

Please advise.
Posted by Charles [MSFT] on 4/3/2013 at 2:20 PM
Hello Phlegmer,

Please update your SQL installation to the latest available Service Pack (currently SP3). I was able to repoduce this issue on SQL 2008 R2 RTM but not in SQL 2008 R2 SP3.

-Charles Gagnon (
Posted by Microsoft on 2/22/2013 at 11:10 AM
Hello Phlegmer. Thank you for bringing this to our attention. We really do appreciate the feedback. We’ll investigate and get back to you. -Walter A Jokiel, Program Manager, SQL Server (
Posted by SQLWork on 1/10/2013 at 9:24 AM
The error message is an artifact of the earlier failure to create the role. That failure was silent and there is no clue that having a member of the role is what caused the problem. It seems to simply be a bug that should be fixed.