CODEWITHSUNDEEP
sql-serversql-server-2005
buyutec
buyutec

Reputation:

Forgot SQL Server Password

I installed SQL Server 2005 sometime ago and forgot the administrator password I set during setup. How can I connect to SQL server now?

EDIT: I think I only allowed Sql Server Authentication. Login with integrated security also does not work.

Upvotes: 18

Views: 117046

Answers (6)

TheGameiswar
TheGameiswar

Reputation: 28900

For these steps to work, you need to be an admin on box where SQL Server is installed.

  • Stop SQL Server from configuration manager
  • Start SQL Server in single user mode

  • Add this -mSQLCMD command as one of the start up parameters, by right clicking the service and going into startup parameters section as shown in screenshot below and start SQL Server

    enter image description here

  • Now connect to SQLSERVER from SQLCMD (open command prompt as admin) like below:

    sqlcmd -s servername\instancename

    And run below command:

    USE [master]
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Administrators', @rolename = N'sysadmin'
GO

    Above command adds all local users as sysadmins. You may change this and add a user like or you can make another user sysadmin and remove BUILTIN\Administrators

  • Once this is done, you need to now stop SQL Server and remove -mSQLCMD parameter and restart SQL Server

References:
Help : I lost sa password and no one has System Administrator (SysAdmin) permission. What should I do?

Upvotes: 1

Ilia Barahovsky
Ilia Barahovsky

Reputation: 10498

If there's no other user with sysadmin privileges but sa, SQL Server should be restarted with -m option for single-user mode. Then you can connect to this SQL Server instance and you're able to add other users with sysadmin role or to execute exec sp_password.

Pinal Dave explains here - http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/ - how to add -m with SQL Server Configuration Manager: right-click server service, go to Advanced tab and add -m; (notice semicolon) in Startup Parameters.

Another way is to stop SQL Server instance in Services and to run it manually from the command line like this:

"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -m -sSQLEXPRESS

I had to do that for SQL Server 2008 as it didn't appear in SQL Server 2008 R2 Configuration Manager. Exect command line can be found with in service properties.

Upvotes: 3

Chris W
Chris W

Reputation: 3314

You don't need to pay for any reset tools to do this. Start the instance in single user mode and you can create a new login with sysadmin permissions/

See this Pinal Dave link.

Upvotes: 9

cowgod
cowgod

Reputation: 8656

Try running the following commands at the command prompt (assuming your Server name is SQLEXPRESS):

osql -E -S .\SQLEXPRESS
exec sp_password @new='changeme', @loginame='sa'
go
alter login sa enable
go
exit

Once you have completed these steps, try to login with username sa and password changeme.

Upvotes: 35

Sanjay Kumar
Sanjay Kumar

Reputation:

Thanks a lot cowgod

It works Fine. I have Logged in to SQL Server with windows authentication mode. Then opened a new query window. There I typed these two commands by prssing F5.

First run exec sp_password @new='changeme', @loginame='sa'

Then alter login sa enable

Upvotes: 5

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391336

Unless you set it up to only accept SQL Server authentication, you can log on with integrated security using the administrator user of the domain and/or machine.

Upvotes: 0

Related Questions