Search This Blog

Lost ‘SA’ password,

If you ever lost a SA password, you may have thought your only option is to reinstall SQL and re-attach to the DB’s. However, SQL server provides a much better disaster recovery method which preserves objects and data in the master DB. Members of the server’s Local Administrator’s group can access SQL server by starting it in single-user mode.

1.Enable SQL Single-User Mode

- Open SQL Server Configuration Manager 
- Stop the SQL Server Instance you need to recover the SA password 
- Open the properties on the SQL Server Instance and click on the Advanced tab 
- Change the Startup parameter by adding -m; at the begging of the line and click OK 
- Start the SQL Service Instance

2.Use SQLCMD to add a Sysadmin Account

- Open the command prompt 
- Run sqlcmd and press enter 
- Run a Transact-SQL (Below) command to add an existing account or a new account to the sysadmin server role. Replace DOMAIN\Username with the account you want to add. *NOTE: if you receive errors, I’ve listed some common issues at the end of this article.

EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin'; 
GO

3.Enable Multi user mode

- Open SQL Server Configuration Manager 
- Stop the SQL Server Instance 
- Open the properties on the SQL Server Instance and click on the Advanced tab 
- Change the Startup parameter by removing the -m; at the begging of the line and click OK 
- Start the SQL Service Instance

4.Reset sa Password

- Open Microsoft SQL Server Management Studio and login with the account you added 
- Under the DB, expand Security, then Logins 
- Open the properties for the sa account, and reset the password

No comments: