Resetting a lost SA password with no local admin access

Posted on October 1st, 2009 in Howtos, MSSQL, Windows Admin by Rodney

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.

MSSQL Error 15023: User already exists in current database.

Posted on August 14th, 2008 in Howtos, MSSQL by Rodney

Here’s another quick one.

An annoying thing that can happen with MSSQL Server, when you move a database to a new server by backing it up on one and then restoring it on the other, is that the server can tell you the username already exists, when you attempt to create it. It’s annoying.

The specific error is:

Error 15023: User already exists in current database.

ANyway, here’s how to fix it:

  • 1. Open a new query.
  • 2. Run the query: USE databasename
  • 3. Run the query: EXEC sp_change_users_login ‘Report’ (this will show the users for the DB)
  • 4. Run the query: EXEC sp_change_users_login ‘Auto_Fix’, ‘username‘, NULL, ‘password‘

This should clear the issue up.