Recover Lost/Disabled SA

In the event that the SA account has logins disabled or the [BUILTIN\Administrators] are disabled, the following method can be used to reset the SA login.

Open SQL Server Configuration Manager and right-click the correct SQL Server listed under SQL Server Services. Under Startup Parameters add the -f flag and hit Apply.

Restart the SQL Server service to enable single user mode. This will grant all users who have local administrator access to connect to the SQL Server with the role of sysadmin to recover the SA password.

Mixed-Mode Authentication

In command prompt, you will now be able to connect to the server with SQLCMD. Type the following to enable sa login:

C:\Users\Administrator> SQLCMD -Slocalhost

1> ALTER LOGIN sa enable 2> GO

If you do not know the sa password, then you can create a new system admin account to bypass the original sa password:

1> CREATE LOGIN NewSA WITH PASSWORD = 'Password1234'; 2> ALTER SERVER ROLE sysadmin ADD MEMBER NewSA 3> GO

You can now remove the -f flag from SQL Server Configuration Manager and restart the database to enable multi-user mode again. You can then login with the login specified above with the password that you chose.

Windows Authentication Only

In the event that you have not enabled mixed-mode authentication, the following steps can be used to create a windows user login, remember the replace the computer name and login with yours (or alternatively type 'whoami' in command prompt):

C:\Users\Administrator> SQLCMD -Slocalhost

1> CREATE LOGIN [WIN-PC-NAMEHERE\username] FROM WINDOWS 2> GO 1> ALTER SERVER ROLE sysadmin ADD MEMBER [WIN-PC-NAMEHERE\username] 2> GO

If you want to add a Windows group login, then you can follow the above steps and specify a group instead:

1> CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS 2> GO 1> ALTER SERVER ROLE sysadmin ADD MEMBER [BUILTIN\Administrators] 2> GO

Similarly to above, remove the -f flag and restart the services to enable access to the SQL server.