Gaining Access to SQL Server When Locked Out

Getting locked out of a SQL Server instance can happen due to a number of situations. One of the most common scenarios I’ve encountered is when a SQL Server is moved from one domain to another without the domain being trusted or having a local SQL admin account. I recently encountered another incident where a DBA was attempting to fail over a log-shipped instance to another instance. Part of their run book included a script to disable a set of users to block production access to the instance. The problem was, the production instance was on a cluster, unlike the development and QA systems where the script had been tested. Disabling the users in production took down the instance preventing the tail log backups from occurring. We had to get the instance back up in order to take those final backups.

What do you do if you find that you’re locked out of a SQL Server instance? The process I’ve been using for the past 10 years is to start SQL Server in single user mode, launch SQLCMD and create a new user with the system admin role. In the recent case I just mentioned, we just had to run an ALTER statement to enable the needed account. Once I have the proper account, I can restart the SQL Server Service and do what I need to do.

What are the steps to start SQL Server in single user mode and create a new sysadmin account?

I open a command prompt as an administrator with a Windows account that is a local admin. For a default instance I would then go through the following steps minus the (” “)
“net stop mssqlserver” and press enter
“net start mssqlserver /m” and press enter
“sqlcmd” and press enter
“CREATE LOGIN security WITH PASSWORD = ‘Rec0very123’ ” and press enter
“GO” and press enter
“sp_addsrvrolemember ‘security’, ‘sysadmin’ ” and press enter
“GO” and press enter
“EXIT” and press enter
“net stop mssqlserver” and press enter
“net start mssqlserver” and press enter

I’ve now stopped and restarted mssql in single user mode, created a new login called security and given the user security system admin rights. SQL Server Service was then stopped and started and put back in multi user mode. At this point, I can log in with the security user and complete whatever task that needs to be completed.

How is a named instance any different? You’ll need to look at the services to determine the actual service name. MSSQL$SQL2017 for example.
For a named instance I would then go through the following steps minus the (” “)

“net stop MSSQL$SQL2017 ” and press enter
“net start MSSQL$SQL2017 /m” and press enter
“sqlcmd -Slocalhost\sql2017” and press enter – localhost can also be the server name
“CREATE LOGIN security WITH PASSWORD = ‘Rec0very123’ ” and press enter
“GO” and press enter
“sp_addsrvrolemember ‘security’, ‘sysadmin’ ” and press enter
“GO” and press enter
“EXIT” and press enter
“net stop MSSQL$SQL2017 ” and press enter
“net start MSSQL$SQL2017 ” and press enter

I’ve included a screenshot of going through the steps on a default instance. For a named instance, you’ll just have to specify the named instance service name to start/stop the service, as well as the machine and instance name when connecting via SQLCMD.