locked
[URGENT] - SQL Server user group gone RRS feed

  • Question

  • we have been having some problems with out active directory, and as there are not many users etc. decided to just rebuild it


    we completely forgot that SQL server had setup user groups in it, and we do not know of any sql-authentication accounts, need access NOW!

    there is a lot of data in this database and we cannot afford to loose it.

    please reply asap.

    would a repar install fix this, does SQL server 08 pro even support a "repair" install
    Thanks From Joe
    Sunday, May 17, 2009 2:30 PM

Answers

  • right
    1) start command prompt
    2) net stop mssql$[instance name]
    3) net start mssql$[instance name] /m
    4) start services.msc
    5) end IIS, all SQL services except the core SQL SERVER([instance name])
    6) go to the management studio, or command prompt sqlcmd
    7) login with windows authentication as a administrative user
    8) add the user as a windows authenticated user
    a. expand security
    b. right click logins
    c. add user
    d. browse
    e. add user
    f. assign relevant rules
    g. OK
    9) reboot server
    Thanks From Joe
    • Marked as answer by Joepitt1991 Tuesday, May 19, 2009 6:12 PM
    • Edited by Joepitt1991 Tuesday, May 19, 2009 6:15 PM posted using Google Chrome, line spaces not held
    Tuesday, May 19, 2009 6:12 PM

All replies


  • First of all if you have administrator privillaged account you can login to SQL Server using windows authentication. Ask system admin to check that first. Nevertheless , take a backup of physical files first. (To take backup of physical files you will have to stop the sql server service then copy MDF ,LDF and NDF (if you have) files of this database to someother machine and even you can attach there.)

    Which version is this? If you have SQL Server 2005 or 2008 follow this Blog by Deepak. http://deepakrangarajan.blogspot.com/2008/01/forgot-sa-password-in-sql-server-2005.html



    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Sunday, May 17, 2009 3:10 PM
  • i am administrator; enterprise admin i looked into this further and realised that the services are not starting if you try to start them manually then it reports that the service failed to respond in a timely fashion sorry for any confusion
    Thanks From Joe
    Sunday, May 17, 2009 4:35 PM
  • What is the Logon Account of the Service. Change that to Local System account and try to start the service. If it start connect to sql server using windows authentication.

    Note : You can copy this database physical (MDF,LDF,NDF) files to another sql server instance (same version or higher versions) and attach .


    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Sunday, May 17, 2009 4:38 PM
  • run as was set to an old domain account, all services started now as local system
    Thanks From Joe
    Sunday, May 17, 2009 7:19 PM
  • logon failed is what i now get TITLE: Connect to Server ------------------------------ Cannot connect to JOE-FF27274009D\HGAADMIN. ------------------------------ ADDITIONAL INFORMATION: Login failed for user 'JKE\Joe'. (Microsoft SQL Server, Error: 18456) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
    Thanks From Joe
    Sunday, May 17, 2009 7:21 PM
  • What is the state in the error?  You will have something like "Error: 18456, Severity: 14, State: 16. "  Did you check the first link i provided http://deepakrangarajan.blogspot.com/2008/01/forgot-sa-password-in-sql-server-2005.html

    Madhu


    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Monday, May 18, 2009 12:35 AM
  • i have started the sql server as follows net start mssql$[instance name] /m the service starts when i try to log on as an administrator i get this. no1 else is logged in. TITLE: Connect to Server ------------------------------ Cannot connect to JOE-FF27274009D\HGAADMIN. ------------------------------ ADDITIONAL INFORMATION: Login failed for user 'JKE\Joe'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
    Thanks From Joe
    • Marked as answer by Joepitt1991 Tuesday, May 19, 2009 5:11 PM
    • Unmarked as answer by Joepitt1991 Tuesday, May 19, 2009 6:13 PM
    Tuesday, May 19, 2009 4:57 PM
  • stopped all but core service logged in added my user account now works fine
    Thanks From Joe
    • Marked as answer by Joepitt1991 Tuesday, May 19, 2009 5:11 PM
    • Unmarked as answer by Joepitt1991 Tuesday, May 19, 2009 6:12 PM
    Tuesday, May 19, 2009 5:10 PM
  • stopped all but core service logged in added my user account now works fine
    Thanks From Joe

    If you could explain a bit more on the solution it would really help others. 

    thanks in advance

    madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Tuesday, May 19, 2009 5:14 PM
  • right
    1) start command prompt
    2) net stop mssql$[instance name]
    3) net start mssql$[instance name] /m
    4) start services.msc
    5) end IIS, all SQL services except the core SQL SERVER([instance name])
    6) go to the management studio, or command prompt sqlcmd
    7) login with windows authentication as a administrative user
    8) add the user as a windows authenticated user
    a. expand security
    b. right click logins
    c. add user
    d. browse
    e. add user
    f. assign relevant rules
    g. OK
    9) reboot server
    Thanks From Joe
    • Marked as answer by Joepitt1991 Tuesday, May 19, 2009 6:12 PM
    • Edited by Joepitt1991 Tuesday, May 19, 2009 6:15 PM posted using Google Chrome, line spaces not held
    Tuesday, May 19, 2009 6:12 PM