SQL2008 R2 Copy Database error "Cannot find the principal SQL2000\Administrator"

Posted on 2011-10-09
Last Modified: 2012-05-12

I'm trying to copy a database from a SQL 2000 box to a SQL 2008 R2 server using SA on both servers.  I am not trying to copy the logons as it's easy enough to recreate them in this case.  

The copy is throwing an error:
Executing the query "EXEC dbo.sp_changedbowner @loginame = N'SQL2000\Ad..." failed with the following error: "Cannot find the principal 'SQL2000\Administrator', because it does not exist or you do not have permission."

I am not sure how that login is getting in the script as I didn't specify it anywhere.

Why is it trying to use the Administrator acct on the SQL 2000 box?  How do I prevent that from happening?


Question by:Ben Conner
    LVL 25

    Expert Comment

    What is the syntax\What is the way you are copying?

    Author Comment

    by:Ben Conner
    I was using the Copy Database wizard using the SQL Management Object method, with ti set to drop existing destination databases.  Removed Login object for transfer.  Summary below:

    Click Finish to perform the following actions:

    Source: sql2000vm SQL Server 2000, Microsoft SQL Server Standard Edition , Build 2039, Microsoft Windows NT 5.0 (2195) NT INTEL X86
    Destination: sql2008r2 Other SQL Server Version, Microsoft SQL Server Enterprise Edition (64-bit) , Build 1600, Microsoft Windows NT 6.1 (7600) NT x64
    Using SMO online transfer
    The following databases will be moved or copied:

    Destination file will be created: d:\MSSQL\DATA\aafw.mdf
    Destination file will be created: d:\MSSQL\DATA\aafw_log.ldf
    Drop existing database and continue transfer

    Package scheduled to run immediately

    LVL 7

    Accepted Solution

    Just do a backup from the 2000 and restore to the 2008 box. That will work fine.

    Author Comment

    by:Ben Conner
    I tried that and it choked.  Then your suggestion triggered something in the back of my mind and sure enough, I'd forgotten to use the overwrite option.  Worked fine.

    Thanks much!

    BTW, it would be nice to understand why the first option didn't work. ??

    LVL 7

    Expert Comment

    If you created the database before the restore, it may have had a problem upgrading in the process. Not sure...just a thought.

    One thing you can do is not create the database at all and let the restore create probably are already aware of that.
    LVL 7

    Expert Comment

    Also note that even though the users added, it would be best to remove them, then reassign access based on the users in the new servers security list. You likely will have some authority errors if you do not do this.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Using of  resource governer in sql2014 2 34
    Compare data between two databases 16 69
    SQL Question 9 32
    SQL Select Query problems 10 36
    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now