DANGER! DropObjectsFirst and CopyAllObjects on the same server
Published Mar 25 2019 01:55 PM 66 Views
Not applicable
First published on MSDN on Feb 28, 2007

A post in the forums had me try out what turned to be a dangerous combination of flags in the Transfer SQL Objects task. The user said they were no longer able to access their database after running their package, and I was curious as to why this would be... It turned out to be a logic problem with the DropObjectsFirst and CopyAllObjects flags.

CopyAllObjects will copy all objects (go figure) associated with the Source Database. This includes all Login objects. In this case, the user was copying his database to the same server, with a new table name. Since DropObjectsFirst was set to True, the Login objects were flagged to be copied, and we end up deleting the logins before doing the copy! This put the server in a bad state of course, which not only causes the task to fail, but prevents anyone from logging into the server from that point on.

I did a little research, and it turns out this is an issue that is scheduled to be fixed in Katmai. It's not a bug, per se – it's more of "unintended consequence" that isn't very obvious (as the user who wrote the post said, why does SSIS drop my logins when I just want to copy one database!)

The most likely fix would be to detect that the source and destination servers are the same, and report a validation error that the logins (or other server objects) are set to be dropped. This will hopefully prevent people from nuking their servers in the future.

Version history
Last update:
‎Mar 25 2019 01:55 PM
Updated by: