Home Dashboard Directory Help
Search

Copy Task fails if user created Roles have members by Phlegmer


Status: 

Closed
 as Not Reproducible Help for as Not Reproducible


3
0
Sign in
to vote
Type: Bug
ID: 776161
Opened: 1/9/2013 12:16:07 PM
Access Restriction: Public
0
Workaround(s)
view
3
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
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 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.
Sign in to post a workaround.