Resetting a lost SA password with no local admin access
I just came across a situation in a client site where an SQLExpress (2008) instance was stood up by an unknown person, who did not leave an SA password and removed the local admins from SA access. He then left the organisation and is no longer contactable.
So how to get back access to the SA account?
1. Firstly, we need to shutdown the SQL instance and set the system into SingleUser mode. This is done as follows:
a. Run the SQL Configuration Manager.
b. Right click on the relevant instance and select "properties".
c. Select the advanced tab.
d. Select the start up option and append ";-m" (no spaces and no ") to the end of the line.
e. Say ok.
f. Stop and start the instance.
2. Now go to the command prompt and reset the password:
a. Start > run > cmd
b. At the command prompt, run "osql -S Servername\SQLInstanceName –E".
c. This will open a prompt window with a "1>" in it. Use these commands.
d. 1> sp_password NULL,’new_password’,’sa’
e. 2> go
f. 3> quit
3. Now go back to the SQL Configuration Manager and undo the above change (stop the service, remove the “;-m” and restart the service.
4. Tada – SQL SA password is changed, even without the local admin group being SA enabled.
on June 9th, 2010 at 5:49 pm
I had to create a shortcut to cmd and run as admin to get this to work.
on June 9th, 2010 at 5:57 pm
also needed the command
alter login sa enable
on June 9th, 2010 at 6:13 pm
drat also needed to enable mixed mode with this reg key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer
changed from 1 to 2 then restart service again.
on July 8th, 2010 at 9:49 pm
Yeah sorry, I should have mentioned, it’s assumed you’re running all the above as a suitably privileged (i.e. administrator) user.
The mixed mode reg key is a good point to0 – again I made the assumption that SQL is in mixed mode.
Good points, Rob.