Post reply

Migrate sql server 2000 to sql server 2008 r2

  • March 28, 2011 at 5:53 am

    #237538

    Hi

    I am migrating several databases from sql 2000 to sql 2008 r2 - using backup and restore. The databases are being created ok. However I have one user 'ctuser' that happens to own a number of tables and stored procedures on the 2000 version of the database. A schema of the same name is being created on the 2008 restored database.

    We currently connect to the database (sql2000) using this user and are keen to continue to do so - due to the number of connections that would need to be changed.

    I have created the ctuser login in 2008 and tried to map this to the user ctuser in the database. However I get an error saying the use already exists in the database. When I try and delete the user from the database I cannot as it owns the schema. Trying to remove the schema obviously fails as there are objects in it. I do not particularly want to change the schema of these objects - as I am not sure of the effect it may have on any referencing applications. I have also tried to run the sp_change_user_logins ( as the login on the server and the user in the database ) have the same id, but no rows are updated. Any ideas please.

    Hope this makes sense

  • March 28, 2011 at 6:02 am

    #1304294

    Try alter user first.

    ALTER USER ctuser WITH LOGIN = ctuser

    That should fix the orphaned login without the need to go through all sorts of convoluted steps.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 28, 2011 at 7:36 am

    #1304343

    Thanks Gail. That worked like a dream:-)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply