locked
Locked out of SQL 2008 Server RRS feed

  • Question

  • The situation is as follows:
    
    - Client is running SQL Server 2008 on a Windows 2008 Standard server 
    (Hpserver1)
    - HPServer1 is a member server in the domain
    - Developers use HPServer1\Administrator account to manage server via SQL 
    Management Studio Express
    - I do not know if the server was configured to authenticate in mixed-mode 
    or Windows-only mode
    - I do not know the SA password (if there was one)
    
    - HPServer1 is promoted to be a domain controller in the Windows domain
    
    - Developers have lost the ability to manage the server via SQL Management 
    Studio Express because the HPServer1\Administrator user ID no longer exists 
    and they do not know the SA password
    
    - No domain accounts with administrative rights are able to access the SQL 
    server for management purposes either.
    
    - I demote the HPServer1 back to member server and set up the new local 
    Administrator account with the same password as before.
    
    - The new HPServer1\Administrator is also unable to access the SQL server 
    via SQL Server Management Studio
    
    - I downloaded and tried the MS SQL Password Unlocker tool from Digitial 
    River and was able to change the SA password in the Master database but the 
    database was still in accessible.
    
    My question is what do we need to do next to regain management access to 
    this database?
    
    
    Friday, October 15, 2010 7:03 PM

Answers

All replies

  • Members of Windows Administrative groups have sysadmin privilege in SQL Server if you start SQL Server 2005 and above in Single user mode.

    Please refer the Forgot the SA password in Sql Server 2005 by Deepak, it may be of help to you.


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Tom Li - MSFT Monday, October 18, 2010 2:35 AM
    Saturday, October 16, 2010 10:16 AM
  • What is exact the error you are getting. What if you create a new domain account (different name ) and add that account to Administrators group of the server, now cannot users connect to the server via that login?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, October 17, 2010 8:20 AM
  • Thanks for the suggestion.  I tried that but it didn't make any difference.   The exact error message is:

     

    Cannot connect to HPServer1\....

     

    Additional Informaion:

    Login failed for user 'domain\user'.  (Microsoft SQL Server, Error: 18456)

    Monday, October 18, 2010 12:48 AM
  • Thank-you for your help!  Switching to single-user mode did the trick.    I was then able to access SQL security and add domain user accounts.
    Monday, October 18, 2010 1:11 AM