When all the logins in a SQL Server are dropped and you don’t know password for sa, you are locked out from accessing your own SQL Server. If you are a member of “Administrators” group on the box, you can breathe easy! You can regain access to the SQL Server.
Follow the steps below to regain access:
- Login into the Box (machine where the SQL Server is installed)
- Open SQL Server Configuration Manager, Go to “SQL Server (MSSQLSERVER)” service properties window > ‘Startup Parameters’ tab and add parameter -m as shown below.
- Restart “SQL Server (MSSQLSERVER)” service. This will start SQL Server in Single User Mode.
- Now, any one user who is a member of ‘Administrators’ group on the box can login into SQL Server using windows user account. In single user mode as only one user can login into the SQL Server, if some administrator has already logged into the SQL Server, another user won’t be able to login.
ROADBLOCK: I faced the situation wherein there was some service which was running under an admin account. Since it was a service which was already running, as soon as I hit restart (step 3, starting SQL Server in Single User Mode), it used to connect to SQL Server before I could login. This is a tough situation and you need to figure out through SQL Server Logs which account is logging in. You can find the location of error log in the startup parameters window above.
TIP: If you are unable to figure out the service/user logging into SQL Server, last thing you can do is place the SSCM and SSMS windows side by side. Keep both the windows ready, hit restart and just before the restarting service progress dialog dismisses, hit login in SSMS.
- To login with SSMS, the usual “Connect” from object explorer doesn’t work as SSMS by design establishes multiple connections. You need to connect with “Database Engine Query” option. It is a button that appears next to “New Query” button in the screenshot below.
- Once you click it, you will see the same connect dialog. Enter “admin:(local)” as server name and login with Windows Authentication. Refer the screenshot below. You might need to Run SSMS as Administrator. The user connecting to the server must be a member of “Administrators” user group on the box.
- Once you are in, run the below commands to add yourself to sysadmins on the SQL Server.
CREATE LOGIN [DOMAIN\username] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\username];
- Remove the startup parameter -m added in 2nd step and restart the SQL Server.
Related MSDN Link: https://msdn.microsoft.com/en-in/library/dd207004.aspx
Hope this helps.