Home / server / Questions / 520721
Accepted
Sarah Weinberger
Asked: 2013-07-04 18:12:20 +0800 CST

SQL Server: Cannot log into SQL Server Management Studio on Server

  • 772

I installed SQL Server 2012 on a Windows Server 2012.

I am going back a few days later and unable to log into SQL Server Management Studio (SMS) using Windows Authentication, even if I launch SMS using the "Run as Admin". I keep receiving error 18456.

Sadly, I did not write down the mode that I set during the installation or the user name that I created, if any. I should have, but did not.

The event viewer shows a bit more detail. The entry is:

Login failed for user '\Administrator', Reason: Could not find a login matching the name provided. (CLIENT: )

Since SMS is not an option for anything at the moment, how do I restore access? I am logged in using the Administrator account.

sql-server

1 Answers

  1. Best Answer

    It looks like you can do this the same way in 2012 that you do in 2008.

    1. Shut down SQL Server in Services.
    2. Launch SQL Server in single user mode (navigate to the directory with an elevated command prompt and type "SQLServr.Exe –m"
    3. Start a new command line prompt.
    4. Connect with sqlcmd. ("sqlcmd -S.", unless there's an instance name, then "sqlcmd -sServername\instance")

    5. Create a new login for yourself on the command line:

      CREATE LOGIN [MYOFFICE-SERVER\Administrator] FROM WINDOWS; GO

    6. Add that user to the sysadmin group:

      exec SP_ADDSRVROLEMEMBER 'MYOFFICE-SERVER\Administrator','SYSADMIN'; GO

    7. The "GO" is important. It should acknowledge that "GO."

    Good luck!

    • 0