Copy Task fails if user created Roles have members

Copy Task fails if user created Roles have members by Phlegmer

Closed

as Not Reproducible Help for as Not Reproducible

Type:	Bug
ID:	776161
Opened:	1/9/2013 12:16:07 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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.

Details (expand)

English

SQL Server 2008 R2

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows Server 2008 R2 Standard (SP1)

US English

Create a custom role

Add members to the role

Run the Copy Task wizard and try to copy the db with the custom role from one server to another.

Anticipate the error.

db copies sans tables after the error:

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.

Copy the db from one server to another sans errors.

X64

Other (e.g. VM Ware, specify in Description)

0 attachments

Posted by Microsoft on 4/5/2013 at 8:40 AM

Hello Phlegmer,

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

Thanks,
-Charles Gagnon (chgagnon@microsoft.com)

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, http://support.microsoft.com/kb/2527041, the latest is SP2.

Please advise.
Thanks
Brendan

Posted by Microsoft 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.

Thanks,
-Charles Gagnon (chgagnon@microsoft.com)

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 (wajokiel@microsoft.com)

Posted by Russell Fields 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.