locked
Can't log in to SQL Server 2008 r2 after my Windows Profile changed - Help? RRS feed

  • Question

  • Hi,

    I recently upgraded my Windows SBS 2003 box to 2011 (clean install of 2011) and had to disconnect from my old server domain and when the new server was up and running had to join the new version of my domain.

    I run a developer copy of SQL Server 2008 r2 on my own PC (not on the SBS box) which uses Windows authentication. When I now try and log on to it, I am getting rejected I assume because even though the domain name and the account name and password are the same, SQL know that this is a different user to the old one somehow?

    Is there any way I can get back in and get SQL to accept my new version user name and password or do I have to de-install SQL Server and re-attach to the data files?

    Siv


    Graham Sivill - Martley, Worcester. UK
    Thursday, November 24, 2011 11:27 AM

Answers

  • Hi,

    I'd try to log in with sa first and re-map the user SID using the sp_change_users_login. It may work if you set mixed mode authentication. Other option is rebuilding the master database. In this case you need to add again the user and attach all user databases. Worst case I'd rebuild/reinstall the complete SQL instance.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Edited by Janos BerkeMVP Thursday, November 24, 2011 12:00 PM
    • Marked as answer by Siv Thursday, November 24, 2011 12:16 PM
    Thursday, November 24, 2011 12:00 PM

All replies

  • Hi,

    The source of the problem is SID. Your old domain controller maintained a SID for your user account, but in the new one the SID is totally different. Your only option to get your permission back is to add again your login to your SQL instance. SQL Server does not store your windows password, it stores your sid only.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Thursday, November 24, 2011 11:33 AM
  • Janos,

    I can't as the user I would use to do that is mine and that is locked out due to the SID issue.

    Even if I did have another user I could use, it would be the same as all my user accounts have been created afresh on my new SBS box.

    So it looks like I am going to have to rebuild SQL then?

    Siv


    Graham Sivill - Martley, Worcester. UK
    Thursday, November 24, 2011 11:37 AM
  • Hi,

    I'd try to log in with sa first and re-map the user SID using the sp_change_users_login. It may work if you set mixed mode authentication. Other option is rebuilding the master database. In this case you need to add again the user and attach all user databases. Worst case I'd rebuild/reinstall the complete SQL instance.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Edited by Janos BerkeMVP Thursday, November 24, 2011 12:00 PM
    • Marked as answer by Siv Thursday, November 24, 2011 12:16 PM
    Thursday, November 24, 2011 12:00 PM
  • Janos,

    I am going to have to uninstall and re-install as I can't log into it in any way.

    Thanks for your help, I was afraid this was going to be the answer.

    Siv


    Graham Sivill - Martley, Worcester. UK
    Thursday, November 24, 2011 12:15 PM
  • You can stop SQL Server and restart it in single user mode.  Once started in single user mode - you can then login and drop the existing windows user and add the new windows user.

    http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx


    Jeff Williams
    Thursday, November 24, 2011 4:15 PM
  • Jeff,

    I wish you'd spotted this sooner, I already removed and re-installed SQL Server 2008 r2.

    Thanks anyway, I have kept the text from that blog post so if this ever hapens again I know how to get round it.

    Siv


    Graham Sivill - Martley, Worcester. UK
    Thursday, November 24, 2011 6:57 PM
  • Good catch Jeff.
    There are 10 type of people. Those who understand binary and those who do not.
    My Blog
    Friday, November 25, 2011 7:59 AM