7

I need to recover my SQL Server 2008 user/pwd.

The situation is: I don't know the SA password and Windows Authenticated user is no longer active user (disable user because the user has left job) so I can not log in using Windows authentication.

Is there anyway to reset the sa password, Windows authentication and to log in to the database?

Last option could be uninstall and install again. In this case data will be lost.

Thanks,

marked as duplicate by Aaron Bertrand Aug 27 '15 at 18:57

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from stackoverflow.com Mar 26 '14 at 17:39

This question came from our site for professional and enthusiast programmers.

14

Follow the steps in Connect to SQL Server When System Administrators Are Locked Out:

  • start the server in maintenance mode with -m
  • connect as local administrator
  • add necessary accounts
  • restart the server in normal mode

Is all described, with detail step-by-step, in the linked article.

10

Here is another workaround:

  1. Run the SQL Server Management Studio as the system account. This can be done easily with the PsExec program. Download PsExec from Sysinternals website. Extract the PsExec.exe program and save it to a folder on your computer such as C:.
  2. Open an elevated Command Prompt and run the following command:

    C:\PsExec.exe -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tool\Binn\ManagementStudio\Ssms.exe"

  3. It will start SQL Server Management Studio as the system account. You’ll see that the username is shown as "NT AUTHORITY\SYSTEM". Simply choose the Windows Authentication mode and click on the Connect button.

  4. Once you connect, you can reset SA password with ease.

If this also doesn't work, the last resort is try some third-party software such as SQL Server Password Changer, or reinstall your SQL Server.

4

Use the dedicated administrator connection (DAC). From command line:

osql -S server\instance -E
EXEC sp_password NULL, 'password', 'sa';
GO

You should probably do this with a Windows Administrator account.

  • This requires Windows Authentication but SQL Server was installed using other Admin user so it is not allowing. – par Feb 10 '14 at 8:21
  • I do not have an environment to verify at this time, but if I am not mistaken any account in the Administrators group will work. You said that at worst you could reinstall, so there must be an account with Administrator privileges available. – Dono Feb 10 '14 at 8:34
  • yes, at last reinstall will work. because SQL server was installed with other Admin account which has been disabled and we do not allowed to reactive and reset its password. and using current Admin account it is not allowing Windows Authentication. – par Feb 11 '14 at 4:03
0

First run SQL Server in single-user mode by adding the "-m" parameter to the server's start up parameters. Open the Cmd.exe with "Run as Administrator" Prompt and type the following:

sqlcmd 

Alter login [Login name] with password = 'New password'

GO

SP_addsrvrolemember 'Loginname','SYSADMIN' 

Go 

Exit

Cool...next go and login in your SSMS...it will work....

From there, you can reset your password easily.

Not the answer you're looking for? Browse other questions tagged or ask your own question.