It’s relatively easy to lock yourself out of SQL Server, even after 20 years’ experience managing SQL Server environments I do it myself occasionally when performing infrequent administrative actions – hence this post!
I find the most common reasons are:
- A SQL Server was removed from a domain without thinking about the consequences — maybe you didn’t enable SQL and Windows Authentication or reset the SA password.
- The only account with sysadmin access gets deleted when the person leaves the business — how do you get sysadmin privileges back?
There are several ways to fix it but what follows is what I find to be the simplest route where the least can go wrong.
Step 1 – Start SQL Server in single user mode
Login to Windows as a local administrator and start an administrative command prompt.
Stop the SQL Server service and start in single user mode only allowing connections from SQLCMD:
net stop mssqlserver net start mssqlserver /mSQLCMD
Step 2 – Connect with SQLCMD and reset the SA password or add another account to sysadmin
In the same command prompt run SQLCMD to connect the default instance or use the -S parameter to pass an instance name.
Now you’re connected with sysadmin privileges without being a member of the role and can fix whatever your problem is. Here are some common tasks:
Enable the SA account
ALTER LOGIN sa ENABLE GO
Reset the SA password
ALTER LOGIN sa WITH PASSWORD = 'NewStrongPassword' GO
Give a Windows account access to SQL Server with sysadmin privileges
CREATE LOGIN [Server1\christian] FROM WINDOWS GO ALTER SERVER ROLE sysadmin ADD MEMBER [Server1\christian] GO
Create a new SQL login with sysadmin privileges
CREATE LOGIN christian WITH PASSWORD = 'StrongPassword' GO ALTER SERVER ROLE sysadmin ADD MEMBER [christian] GO
Enable SQL and Windows Authentication mode.
You don’t need to be in with SQLCMD to change this and it’s best done in the registry if you can’t start SQL Server and have issues with Windows accounts. It still has a place here for that reason.
Use regedit to change it so you can make sure the path is correct on your installation. You’re looking for a LoginMode key. By default it’s 1 for Windows Only. Change it to 2 for SQL Server and Windows Authentication.
On my default instance installation of SQL Server 2019 it is here:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer]
You’ll need a service restart.
Step 3 – Clean up
You’ll need to stop and start SQL Server now to get out of single user mode:
net stop mssqlserver net start mssqlserver
You should be able to connect as normal now in Management Studio etc but don’t forget to start SQL Server Agent again if it’s normally running as it will have stopped and won’t restart by itself.
I hope that helps but if you’re out of your depth, have a look at our Managed DBA Service for SQL Server — we’ll take care of your database platform so you can focus on everything else.
