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.