Forgot SA password on SQL Server

Problem

Our developer left and no one knew the sa password for SQL server on his development machine.

Solution

Reset the sa password.

Run SQL in Single User Mode

  1. Open SQL Server Configuration Manager -> SQL Server Services.
  2. Right click on your SQL instance and Stop it.
  3. Right click again on your SQL instance -> Properties -> Advance
  4. Under Startup Parameters, add -m; before -dc
  5. Click OK
  6. Go to SQL Server Network Configuration -> Protocols for <SQL Instance>
  7. Right click Named Pipes, ensure that it is enabled.
  8. Under Pipe Name: Change it to \\.\pipe\sql\query
  9. Start your SQL Instance.

Create a Recovery Account with SysAdmin rights

  1. Open an Elevated Command Prompt
  2. Type: sqlcmd
  3. You should get the prompt, ‘1>’
  4. Type in the following commands in sequence:

CREATE LOGIN recoverpwd WITH PASSWORD = ‘MyPassword1’
Go
exec sp_addsrvrolemember ‘recoverpwd’, ‘sysadmin’
Go
Exit

Login as recoverypwd and reset your sa password.

 

 

Tags:

No comments yet.

Leave a Reply

%d bloggers like this: