Reset SQL Server sa password

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,

score 16 · Answer 1 · edited Jul 16 '21 at 16:53

16

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.

edited Jul 16 '21 at 16:53

Hannah Vernon

70,928
22
177
323

answered Mar 26 '14 at 18:06

Remus Rusanu

52,054
4
96
172

score 14 · Answer 2 · edited Aug 02 '16 at 15:22

Here is another workaround:

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:.
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"
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.
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.

score 4 · Answer 3 · answered Feb 10 '14 at 07:48

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.

answered Feb 10 '14 at 07:48

Dono

161
5

score 0 · Answer 4 · edited Mar 01 '18 at 10:39

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.

Reset SQL Server sa password

4 Answers4