Solution for SA Account Locked account and forgot an administrator password in SQL Server 2008

Dear Friends,

Recently I had issue SQL Server 2008 MY SA Account locked out and not able to login with windows authentication and below was the error





Below are the steps carried out to resolve the issue.

1. Using the SQL Configuration Manager, I stopped the instance I needed to work with.





2. I also stopped any other SQL-related services which might try to connect to the instance, using up my one connection. This included Agent, and might also include Analysis Services or Reporting Services, depending on your installed options.



3. Rightclick on the cmd and select run as administrator

4. From a command prompt, I navigated to the SQL Server folder where the program executable lived.

Eg : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

Note: Rightclick on the cmd and select run as administrator if not the we cannot proceed further

5. I started SQL Server's executable with the -m parameter and waited for it to complete startup and recovery of any databases. (Note that the error log information echoes to the command prompt window but will simply pause when it's done startup - leave this window open to keep SQL Server running in this single-user mode. You can confirm if you successfully launched in single-user mode by looking for a line in the output that reads "SQL Server started in single-user mode. This an informational message only."





6. I used SQLCMD from another command prompt session. With 's' and 'm' switches If you're unfamiliar with SQLCMD, the -S switch specifies the instance name, the -E specifies that you're using Windows Authentication dellr2 can be your domain /system name. These switches are case-sensitive.




7. Once connected, I added a login for my Windows/domain account:






8. Then I used a system stored procedure to add my account to the sysadmin fixed server role.






9. Now that I've let myself back in to SQL Server, I stopped the service with the SHUTDOWN command. I could have closed the other command prompt window as well.



10. Finally, I restarted the service. Since I already had a command prompt window open, I opted for Windows' NET START command. The SQL Configuration Manager, the Windows Services Control Panel, or the Registered Servers pane in SSMS would also have worked.







11. Later I was able to login with the added account and unlock the SA account..