SQL Server – lost sa password

Today I had to log-in as an administrator on a local SQL server but I didn’t know the password because the colleague that used my PC in the past, left the company taking this magic secret with him. Without knowing if it’s even possible to regain access over the sa account I found this very useful post that saved me: Forgot the SA password in Sql Server 2005

I’ll describe below the steps I made just to have them written here but for more details please visit the previous link.

  1. Stop the desired SQL Server instance using the Services console.
  2. Open the command prompt in administrator mode [All Programs -> Accessories -> Right click on Command Prompt -> Run as administrator]
  3. Use the command prompt to start the named instance in single user mode:
    net start MSSQL$<InstanceName> /m SQLCMD
  4. Connect to SQL server from command prompt:
    sqlcmd –S .\InstanceName
  5. Give your windows account sa rights by executing in the command prompt the following script:
    USE [master]
    GO
    CREATE LOGIN [Domain\AccountName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    EXEC master..sp_addsrvrolemember @loginame = N’Domain\AccountName’, @rolename = N’sysadmin’
    GO
    
  6. Stop SQL Server, restart it normally and connect with SQL Server Management Studio using windows authentication. Now you’ll be able to change the sa password.
Advertisements