First off, you need to log into the machine as a user with
local admin rights.
Next, you should open Management Studio on the local sql
server machine and change the default startup condition so that it doesn't
automatically launch the object explorer. You do this by opening Tools -
Options (Environment - General) and selecting something other than Open Object
Explorer in the At startup: option.
For instance, you can set it to Open new query window.
We're disabling the object explorer because it will make a
connection to the local database, and we're going to be setting the database to
use Single User Mode. In that mode, as the name implies, only one connection
can be made to the database at a time. If our object explorer has already used
up that connection (even if it didn't authenticate correctly), then our query
window won't have an available connection and will fail. So, we're avoiding
that issue.
After making the change, close SQL Server Management Studio.
Next, stop all SQL Server services on the server. The
easiest way to do so is to open up the SQL Server Configuration Manager.
Right-click on each Running service and select 'Stop'. When
done, hit F5 or click on the Refresh icon to confirm that all services are in
fact stopped.
Now open a command window as an administrator and type in
the following:
NET START MSSQLSERVER /f /T3608
You should see something like this:
(Note that you can also start the service in single user
mode by adding ";-m" to the Startup Parameters used by the SQL Server
(MSSQLSERVER) service from Sql Server Configuration Manager. If you do so,
remember to remove the change when you're done with these steps)
It's possible that more than one service started up in
response to the above command, in which case the "extra" services may
use up the single connection available. In Sql Server Configuration Manager,
refresh the services, and make sure the only one running is SQL Server
(MSSQLSERVER) or whatever instance it is you're trying to work with. Stop any
other services that are not already Stopped.
Now open SQL Server Management Studio and click New Query
(or use the query window that opened on startup if you configured it to do
so). Type in the following:
Use master
Go
Alter login [sa] with password=N’CHOOSEPASSWORDWISELY’
Go
Alter login [sa] enable
Go
Assuming this runs without error, your sa password should
now be set (hopefully to something secure). Return the SQL Server
Configuration Manager and restart the SQL Server (MSSQLServer) services, and
start any other services you require.
You should now be able to return to SQL Server Configuration
Manager and log in (via query or object explorer) using SQL Server
authentication and the sa user account with the password you specified above.
At which point, you can add whichever windows accounts you need to have access
to the server.
It's generally a best practice not to use the sa account,
and in fact if you've disabled it, that's likely one of the reasons why you
would need the steps described in this article. Be sure to re-disable it once
you have re-established the windows accounts you wish to use to access the
database.
