How do you reset the sa password?

I lost the sa password on a machine, and when I log in to the machine directly using an account in the admin group, SQL Server Management Studio will not allow me to log in using Windows authentication.

My plan was to simply log into the server, connect via Windows Authentication, and reset sa to use a new password. Since I cant' connect via Windows Authentication, this won't work.

How else can I reset the sa password?

You can follow the steps mentioned in the link below to reset the SA password:

• Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005

Steps summarised below:

• Open SQL Server Configuration Manager from Start Menu > Programs > Microsoft SQL Server 20xx > Configuration Tools > relevant to the newest version of SQL Server you have installed (e.g. if you have 2005 and 2012 installed, use the 2012 version). Don't have a Start Menu? On Windows 8's Start screen, start typing SQL Server Con... until it shows up.

• Stop the SQL Server instance you need to recover by right-clicking the instance in SQL Server Services and selecting "Stop"

• Right-click the instance you just stopped, click Properties, and in the “Advanced” tab, in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option (on newer versions, you can go directly to the "Startup Parameters" tab, type "-m" and click Add, without worrying about the syntax, the semi-colon, or anything else).

• Click the “OK” button, and restart the SQL Server Instance

• After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login (or a newly created one) to the sysadmin server role.

The following example adds the account "Buck" in the "CONTOSO" domain to the sysadmin role:

EXEC sp_addsrvrolemember 'CONTOSO\Buck', 'sysadmin';

Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server instance one more time.

NOTE: make sure there is no space between “;” and “-m”, the registry
parameter parser is sensitive to such typos. You should see an entry
in the SQL Server ERRORLOG file that says “SQL Server started in
single-user mode.”

Additional resources:

• Connect to SQL Server When System Administrators Are Locked Out

• Leveraging Service SIDs to Logon to SQL Server 2012 and SQL Server 2014 Instances with Sysadmin Privileges

• Recover access to a SQL Server instance using PsExec

Ultimately, you could always copy the database files to another instance, or even reinstall SQL Server (adding a local account as sysadmin during that process).

StackExchange Database Administrators Q#4679, answer score: 11