Reset SQL Server sa password [duplicate]

Question

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,

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.