Post reply

Upgrade to 2008 from 2005 failed due to sa account being renamed

  • June 22, 2010 at 1:51 pm

    #145734

    Hi all,

    I was upgrading one of the named instance of SQL 2005 on my test box and it failed due to the sa account being named to something else.

    I did not know this until I checked the error in microsoft website that if sa account being renamed to something else, the upgrade will fail.

    now I even got the solution about how to resolve this issue.

    http://support.microsoft.com/kb/960781

    But, I dont know how to start a named instance through command propmt and renamed the sa account back for that particular instance.

    Please help me.

    Thanks in advance

    Sree

  • June 22, 2010 at 2:28 pm

    #1183588

    You can start a name instance from command prompt like this:

    net start <InstanceName>

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • June 22, 2010 at 2:40 pm

    #1183593

    Thank you for your reply !!

    I was able to start the named instance but now I am not able to alter the login name

    I mean I getting error while running this :

    sqlcmd -E -A -Q"alter login sysdba1 with name = sa"

    where sysdba1 is the name to which sa has been renamed.

  • June 23, 2010 at 12:19 am

    #1183733

    sree-879715 (6/22/2010)

    Thank you for your reply !!

    I was able to start the named instance but now I am not able to alter the login name

    I mean I getting error while running this :

    sqlcmd -E -A -Q"alter login sysdba1 with name = sa"

    where sysdba1 is the name to which sa has been renamed.

    Check this location for you problem....!

    You must do more things after you run the sqlcmd command, for example shutdown the SQL Server, then run the feature of setup through the SQL Server Installation Center...etc!

    Check all the details at the link above.

    Then let me know what happened!?

    😉

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • June 23, 2010 at 8:19 am

    #1183962

    Thank you for the reply

    I checked everything and tried again but not able to run the script to alter the sa login name

    Please help me in getting tht in correct way.

  • June 23, 2010 at 8:22 am

    #1183967

    sree-879715 (6/23/2010)

    I checked everything and tried again but not able to run the script to alter the sa login name

    Why not?

    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

  • June 23, 2010 at 8:34 am

    #1183980

    when I am trying to rename the sa account back using this command :

    sqlcmd -E -A -Q"alter login sysdba1 with name = sa"

    getting error as incorrect syntax near login

    Not sure what I am missing ..please help me in resolving this

  • June 23, 2010 at 9:06 am

    #1184017

    Maybe try:

    sqlcmd -E -A -Q"alter login [sysdba1] with name = sa;"

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • June 23, 2010 at 9:13 am

    #1184022

    I tried even this but same error..incorrect syntax near login

  • June 23, 2010 at 12:12 pm

    #1184132

    Maybe try it in two parts. First sqlcmd -E -A -Q to run SQLCMD and log in, then, as a second step, run the alter login statement

    alter login [sysdba1] with name = sa;

    GO

    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

  • June 23, 2010 at 12:20 pm

    #1184139

    I tried this even...but nup same kind of errors

  • June 23, 2010 at 12:29 pm

    #1184143

    Ok, log into SQLCMD and run the following please

    SELECT @@Version;

    GO

    What's the result?

    Do note that with the SQLCMD switches you have, you're connecting to the default instance on the machine you're running that on. Is that where you want to connect to?

    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

  • June 23, 2010 at 12:55 pm

    #1184156

    We have one default and 4 named instance on that machine and I am trying to upgrade one of the named instance. I think I am doing something wrong at this stage, if so

    Please help me in connecting to my named instance and run that alter script to rename the sa account.

  • June 23, 2010 at 1:13 pm

    #1184163

    Yup, you're connecting to the default instance. Check the SQLCMD help and fix the settings to connect to the desired named instance. The alter login script is correct.

    http://msdn.microsoft.com/en-us/library/ms162773.aspx

    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

  • June 23, 2010 at 2:09 pm

    #1184187

    Thank you very much for your help.

    I finished the upgrade successfully

    The commands I used to resolve this issue are :

    net start $MSSQLINSTANCENAME

    sqlcmd -S servername\instancename -E -A -Q"alter login <sys-admin> with name=sa"

    net stop MSSQL$INSTANCENAME

    Thats all

    Thank you once agian

Viewing 15 posts - 1 through 14 (of 14 total)

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