How to Safely Disconnect All Users from a SQL Server Database
There are situations when you need exclusive access to a SQL Server database—such as during maintenance, migration, or backup tasks. To achieve this, you may need to disconnect all existing users. Fortunately, SQL Server provides a safe and effective way to do this across most versions and editions.
Below is a simple method to switch a database to single-user mode, which immediately disconnects all users and rolls back any existing transactions.
Step 1: Set the Database to SINGLE_USER Mode
This command forces the database into single-user mode, rolling back any active connections immediately.
ALTER DATABASE <DatabaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE <DatabaseName>;
GOOnce executed, only a single user (typically your current session) can connect to the database. This allows you to safely perform your administrative tasks without interference.
Step 2: Perform Your Maintenance Task
Carry out any required operations such as schema updates, data fixes, or backup procedures while the database is in single-user mode.
Step 3: Return the Database to MULTI_USER Mode
After completing your tasks, restore normal access by switching the database back to multi-user mode:
ALTER DATABASE <DatabaseName> SET MULTI_USER;
GONote on Rollbacks
In some cases, you may need to wait a few moments for SQL Server to finish rolling back active transactions after setting the database to single-user mode. Be patient and allow SQL Server to complete this process to avoid data corruption or partial changes.
This approach is reliable and recommended when you need full control over a SQL Server database, especially during critical operations. Always remember to revert to MULTI_USER mode to restore access for other users once your work is done.
