Joseph Michael Pesch
VP Programming

Forgot SQL Server Login

by 27. June 2011 08:52
On my current computer I had installed SQL Server 2008 locally some time back and forgot the "sa" password I had assigned at the time of installation and could not find it documented anywhere.  Also, it seems I did not add my windows account as an administrator as I could not login with integrated security even using the windows account that has local administrator access on the machine.  To solve this problem, I stopped the SQL service, added the "single user mode" parameter to the startup parameters, restarted the service, and changed the "sa" password using a SQL query window (SQL command shown below).
To add the "single user mode" startup parameter, open the "Sql Server Configuration Manager", right click on the "SQL Server (INSTANCENAMEHERE)" service and select "Properties", then click on the "Advanced" tab, scroll down to the "Startup Parameters" and add "-m;" (without the quotes) to the front of the existing parameters.
Restart the service, then through Sql Management Studio, open a new query window and change the connection string to your Sql Instance and run the following commands.  Note: You can put whatever new password you want in the @new parameter.  Once that is finished, go back and remove the "-m;" from the startup parameters, restart the service and login with your "sa" account and the new password you assigned.
exec sp_password @new='NewPasswordHere', @loginame='sa' 
go 
alter login sa 
enable 
go
Alternatively, if you cannot even login to the server, perform the following:
  1. Start the instance of SQL Server in single-user mode by using either the -m; or -f; options.
  2. Start command prompt as local administrator
  3. Connect to the server: sqlcmd -E -S SERVER_NAME_HERE
  4. Add yourself (computername\username) to the sysadmin SQL group with the following commands:
    • create login [computername\username] from windows;
    •  exec sp_addsrvrolemember 'computername\username', 'sysadmin';
  5. Restart the server in normal mode

Tags:

SQL Server

Comments are closed