Why Use Single User Mode?

The main reasons:

• Emergency Repairs
  • Useful when the database is suspect, corrupt, or otherwise inaccessible.
  • Allows running commands like DBCC CHECKDB or restoring from backup.
• Restore or Rollback Operations
  • Ensures no other users interfere during a restore, rollback, or schema change.
• Change Database Settings
  • Some settings (like changing the database owner or collation) require exclusive access.
• Remove Users or Fix Permissions
  • Helps when you need to kick out all users to fix login or permission issues.

How to Start SQL Server in Single User Mode

You can start SQL Server in single-user mode by adding the -m startup parameter:

Using SQL Server Configuration Manager:

• Open SQL Server Configuration Manager.
• Go to SQL Server Services.
• Right-click your SQL Server instance → Properties.
• In the Startup Parameters, add:
  • -m
• Restart the SQL Server service.

Optionally, you can specify a program to allow only that program to connect, e.g. -m”SQLCMD” (straight quotes, and the program name is case-sensitive).

• Stop SQL Server Agent first, or it may take the only connection.
• When you’ve finished, remove -m from the start-up parameters and restart the service, or the instance stays in single-user mode.

Single-user mode for one database

To get exclusive access to one database rather than the whole instance (NEEDS APPROVAL, this disconnects everyone and rolls back their open transactions):

ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- do the work
ALTER DATABASE [YourDB] SET MULTI_USER;

From my SQL Server DBA handbook.