Reset Sa Password in SQL Server 2008, 2008 R2

Below steps helps to reset the "sa" password if you forgot or lost without install SQL Server 

1. Login into server using Administrator login

2. Open SQL Server Configuration Manager

3. Select SQL Service - Properties - Advanced -- Need to add (-m;) parameter to startup parameter of the

SQL Service, to start SQL Server in single user mode to reset the "sa" password

Note - Dont give any space after semicolon. Add -m; parameter starting itself it looks like below

-m;-dc:\program..........

4 Restart the SQL Service

5. Open Command Prompt

6. Run SQLCMD

If you have two instances running on server say SQL 2008 R2, SQL 2012, then issue command as below

7. SQLCMD  -bhamare3\Named

After invoking the SQLCMD it should display 1> prompt indicates login into server with admin login

Possible Errors:

You may receive the below error when you run SQLCMD from command prompt

Login failed for domain\user or user doesnot have sysadmin permissions

If you got the above error please stop the SQL Service and change the built-in account to Local System and start the service in single user mode and run the below steps

1> select @@servername

2>go

1> create login [domain\PC3] for windows;

2> go

1> sp_addsrvrolemember 'domain\PC3','sysadmin';

2> go

If NP (named pipes) is not enabled in your system you may encounter the below error

C:\Users\bhamare3>sqlcmd

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

Then Enable the Named Pipes protocol from SQL Server configuration manager then again invoke 7th step

Issue the below commands to enable / reset sa password

1> select @@servername --> Make Sure Instance name is correct

2> go

1> alter login sa with password='Dhiraj@123'

2> go

1> exit

1> alter login sa enable

2> go

Remove the (-m;) parameter from the SQL Server startup parameters, Restart the SQL Service and then Open SSMS with SQL Authentication. Then change the sa password as you like.