none
Only sysadmin user is SA; Lost SA password RRS feed

  • Question

  • I am running Windows 7 Home Premium and an Instance named SQL_EXPRESS_SP1 (SQL Server 2008 R2)

    The only user that has sysadmin privileges is SA.  The sa account has a password but I cannot find/recall it (Murphy is alive and well)

    I have scoured the articles on this forum and have found a number of "solutions" which address either earlier versions of SQL Expres or earlier versions of Windows or both.  For example there are many "solutions" dealing with SQL Express (pre-2008) on Vista.  They do not work for me.  I do not have anything called SQL Server Surface Area Configuration (or similar name).  I amgoing nuts trying to deal with this, since I cannot administrer this db without the lost sa password, nor can I find any way to bump/add a user into the sysadmin role.

    The other "solutions" seem to not address or solve MY problem.

    Is there an answer short of deleting all SQL Server Express installations and starting from scratch?


    RRR
    Tuesday, February 1, 2011 7:05 PM

Answers

  • Hi,

    Please refer to the following documentation which could resolve this issue and is worked for all SQL Server 2005 and later editions, see:Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out (http://msdn.microsoft.com/en-us/library/dd207004.aspx).

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, February 2, 2011 5:37 AM
    Moderator

All replies

  • hi RRR,

    your SQLExpress instance should be available for "local machine administrators" (meaning local WinNT operating system logins)... local administrators usually belong to sysadmin SQL Server server role... so try connecting via SSMS with such a local admin login.. you can then inspect your permissions... if this is satisfactory, you can finally  access "sa" login's properties and modify it's password accordingly to your needs..

    regards


    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de
    Tuesday, February 1, 2011 10:58 PM
    Moderator
  • Hi,

    Please refer to the following documentation which could resolve this issue and is worked for all SQL Server 2005 and later editions, see:Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out (http://msdn.microsoft.com/en-us/library/dd207004.aspx).

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, February 2, 2011 5:37 AM
    Moderator