12

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,

1
  • For unlocking sa, you just have to connect as a machine administrator to the server. The machine administrators are by default granted access to SQL Server. Once you connect as an administrator, you can run below query:ALTER LOGIN sa WITH PASSWORD = 'new_password' UNLOCK
    – prem30488
    Feb 5 at 14:18
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.

13

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.

2
  • what is the name of the server in this case @user37727 Mar 25 '20 at 12:16
  • Thanks, works a treat!
    – gls123
    May 6 '20 at 14:05
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.

3
  • 1
    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.

1
  • What kind of authentication is required on the sqlcmd line?
    – RonJohn
    Jul 8 '20 at 17:31

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