Sunsick's Tech Blog: [SQL Server] SQL Server in Single-User Mode

[SQL Server] SQL Server in Single-User Mode

When to use?

SQL Server is damaged and can't start normally.
There's a SQL Server trigger and everybody is blocked to login.
You want to do some maintenance work, but you don't want to connect to the server without configuring firewalls or something.

Prerequisite

You are able to connect to the server via RDP.
You are an administrator of the server.
There's a SQL Server trigger and everybody is blocked to login.
You want to do some maintenance work, but you don't want to connect to the server without configuring the firewall or anything.

How to configure

with SQL Server Configuration Manager

Launch SQL Server Configuration Manager

Open SQL Server, Select Properties

Go to "Startup Parameters" and add "-m"

Check and "OK"

Restart SQL Server service

with Command Prompt

Check Service Name, (It's MSSQL$SQL2017 in this case)

use "Net stop" to stop service

net stop MSSQL$SQL2017

use "Net start" to start service with /m option

net start MSSQL$SQL2017 /m

Test

Now you will see the following error message when you're trying to connect to the server

Connect to SQL Server

SQL Server Management Studio

Now, you try to connect to the server as a local administrator

Somehow, you can't login to the server even if you're a local administrator

This is because "Object Explorer" takes a session already.

So, change the environment: Open new query window at Startup

"OK" and close all SSMS

Restart SQL Server

Launch SSMS manually with the options

"C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\ssms"

-S .\SQL2017 -d Master -E

Now it works and you're the only one.

Of course, you can't open another a new query window

SQLCMD

Connect to the server via sqlcmd

sqlcmd -S .\SQL2017 -d Master -E

References

Sunsick's Tech Blog