How to recover the SA password in MSSQL 2008
Have you forgotten the SA (system administrator) password of your MSSQL installation? Can you not login using the Windows administrator account? Relax and do not panic! Here are some of the solutions 🙂
First you should try to login to the server as a domain admin or a local admin and connect to the database with SQL Server Management Studio (SSMA) using Windows Authentication. If this is not working then just continue reading.
If you (or someone else) have disabled the possibility of logging in as a local or domain admin (which is a part of best practice) you will have to enable an SA account, or reset the password of it.
You can enable accounts, reset passwords and add new sysadmin accounts in SQL Server 2005 and SQL Server 2008, the only catch is that you have to do it in the single user mode which means that if it is used in production you have to do it when the database can be down for about 10 minutes.
Note: If the database is used in a production environment I recommend that you do NOT change the password of the SA account unless you are totally sure that no applications are running the database using the SA account. It´s better to add a new account with sysadmin privileges if you don’t want to take the risk.
Follow these steps to add a new sysadmin account in SQL Server 2008 express.
1. Logon to the server with Windows domain or local administrator account.
2. Start the command prompt, using “Run as Administrator” if the server is also Windows 2008, or Windows 7
3. Stop the SQL Server either using the SQL Server Configuration Manager or by running the command:
NET STOP “SQL SERVER (SQLEXPRESS)” And wait till the service is stopped
4. Start the SQL Server in single user mode by running the command*:
NET START “SQL SERVER (SQLEXPRESS)” /m
5. Start SSMA, using “Run as Administrator” if the server is also Windows 2008, or Windows 7
6. In SSMA click the “New Query” button and login to the instance localhostsqlexpress with “Windows Authentication”.
7. In the new query window you run the following command to check the privilege of logged on user:
SELECT user_name()
–the result should be “dbo” which means you are logged in as a sysadmin
8. Comment out the previous command or delete it, then run the following command if in a domain:
sp_addsrvrolemember ‘domainname\username’,’sysadmin’
— where domainname is the domain you are in.
Example of user jimi in the newsweb domain:
sp_addsrvrolemember ‘newsweb\jimi’,’sysadmin’
If the account is local then change the domainname to the servername like in this example:
sp_addsrvrolemember ‘laptop-jimi-w7\jimi’,’sysadmin’
9. Stop the SQL Server, either using the SQL Server Configuration Manager or by running the command:
NET STOP “SQL SERVER (SQLEXPRESS)” And wait till the service is stopped
10. Start the SQL Server, either using the SQL Server Configuration Manager or by running the command:
NET START “SQL SERVER (SQLEXPRESS)” And wait till the service is started
11. Now you should be able to login with the newly added account in SSMS and from there you can add, remove and reset accounts when the database is online.
*NET commands used to start and stop the sql service depend on the installation.
To start a named instance you can run one of the following command lines (and this is how we do it on the sql express 2008):
NET START “SQL Server (instanceName)”
NET START MSSQL$instanceName
To start a default instance you can run: NET START MSSQLSERVER