Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
To disconnect all active connections from a Microsoft SQL Server database, connect to master, switch the target database to SINGLE_USER with ROLLBACK IMMEDIATE, perform your maintenance, then switch it back to MULTI_USER. This disconnects everyone from that database—not just one person—and can roll back uncommitted work. If you need to end only one session, use KILL instead.
The two-step SQL Server method
Use this for a planned operation that needs exclusive access to the whole database, such as a restore, rename, detach, or controlled maintenance. Replace [YourDatabaseName] with the exact database name. Run the script from a dedicated administrative connection whose database context is master.
USE [master];
GO
ALTER DATABASE [YourDatabaseName]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Perform the required exclusive-access operation here.
ALTER DATABASE [YourDatabaseName]
SET MULTI_USER;
GOThe first command places the database in single-user mode and disconnects other connections. The second command is essential: single-user mode does not automatically end when your session disconnects. Keep the restricted period short and restore MULTI_USER as soon as the task is complete.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Before you run the command
- Confirm the database name. A typo can target the wrong database. Square brackets safely delimit names that contain spaces or special characters.
- Understand the impact.
WITH ROLLBACK IMMEDIATEtells SQL Server not to wait for active transactions to finish. Other connections are terminated and incomplete transactions are rolled back. Committed data is not undone, but uncommitted inserts, updates, deletes, or other work can be lost. A large rollback can still take time; “immediate” does not mean all cleanup finishes instantly. - Plan for service interruption. Users may see errors, jobs can fail, and applications may retry connections. Use a maintenance window where practical, notify affected owners, and pause applications or jobs that would reconnect.
- Use an authorized identity. Microsoft documents
ALTERpermission on the database as the permission requirement for changing its access mode. Your organization may require a DBA role or change approval as well. - Check asynchronous statistics updates. Microsoft advises that
AUTO_UPDATE_STATISTICS_ASYNCbe off before single-user mode, because its background thread can take the one available connection. Check it with:SELECT name, is_auto_update_stats_async_on FROM sys.databases WHERE name = N'YourDatabaseName';If it is on, change it only as part of an approved plan:
#1 Best Overall
ALTER DATABASE [YourDatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
Microsoft’s single-user mode guidance explains the behavior, permission, and connection considerations.
Why connect to master?
Single-user mode allows only one connection to the target database. If your query window is connected to that database when you change its mode, your own session can occupy the only slot. SSMS Object Explorer, a monitoring tool, SQL Server Agent, an application pool, or another administrator may also claim it first.
Prepare one dedicated query window connected to the SQL Server instance, set its context to master, and use it for the operation. Close extra SSMS windows or Object Explorer connections to the target database. The master context avoids taking the target database’s slot merely to issue the access-mode command, but it cannot guarantee another tool will not connect first.
Recommended Free Tools
Choose the right scope: everyone or one session?
SINGLE_USER WITH ROLLBACK IMMEDIATE is database-wide. It is appropriate when the whole database needs exclusive access, but excessive if a single blocking or unwanted session is the problem. To inspect active user sessions, run a query such as this from an authorized connection:
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status,
s.login_time,
s.last_request_start_time,
s.last_request_end_time
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
ORDER BY s.session_id;Check the login, host, application, database activity, and any relevant transaction or blocking relationship before acting. Do not terminate a session just because its host or login looks unfamiliar. Once you have verified the session ID, end only that session with:
KILL 57;Replace 57 with the verified session_id. SQL Server may need time to undo that session’s transaction. To check rollback progress for a session being rolled back, use:
KILL 57 WITH STATUSONLY;See Microsoft’s KILL reference for details. Ending a session does not remove its login, revoke permissions, or prevent an application from connecting again.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsAfter maintenance: restore and verify access
Run the second access-mode command from master promptly after the task:
Rank #3
USE [master];
GO
ALTER DATABASE [YourDatabaseName]
SET MULTI_USER;
GOThen check the access mode and database state:
SELECT name, user_access_desc, state_desc
FROM sys.databases
WHERE name = N'YourDatabaseName';The expected access mode is MULTI_USER; the database should normally be ONLINE. You can inspect current user sessions associated with the database as another check:
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status,
DB_NAME(COALESCE(r.database_id, c.database_id)) AS database_name
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c
ON c.session_id = s.session_id
WHERE s.is_user_process = 1
AND DB_NAME(COALESCE(r.database_id, c.database_id)) = N'YourDatabaseName'
ORDER BY s.session_id;DMV results show current session information, not a historical log of every connection that was disconnected. Existing users do not resume their old sessions; they must connect again. Application pools may do that automatically.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Troubleshooting common failures
Another connection takes the single-user slot
Applications with connection pools, health checks, monitoring tools, SQL Server Agent jobs, SSMS Object Explorer, or another administrator may seize the slot. Pause connection sources that can be safely paused, close extra client connections, and run the operation from the prepared administrative session. If AUTO_UPDATE_STATISTICS_ASYNC is enabled, address that setting under your maintenance plan before retrying.
The application reconnects immediately
Single-user mode limits access but does not stop a service from retrying. Pause the application, deployment service, scheduled job, or incoming traffic source before changing the database mode. Resume it after MULTI_USER is restored and verified.
Rank #4
The operation or rollback takes a long time
Terminating a connection does not erase the work SQL Server must undo. A large transaction can take time to roll back. For a targeted KILL, check progress with KILL <session_id> WITH STATUSONLY. Do not assume that an immediate-disconnect request means rollback has already completed.
The administrator cannot get into the database
If a competing connection has claimed the slot, stop or pause competing connection sources, then reconnect through an administrative path with the query context set to master. If you can connect to the instance, restore normal access with ALTER DATABASE [YourDatabaseName] SET MULTI_USER;. If you cannot reconnect, address the connection source that is holding the slot and use your organization’s approved administrative access path.
The database is still in single-user mode
The database remains restricted until the access mode is changed back. From master, run ALTER DATABASE [YourDatabaseName] SET MULTI_USER;, then verify user_access_desc in sys.databases. Check that the correct database name was used and that the command succeeded.
Production checklist
- Is the target database name correct, and do you really need to disconnect everyone?
- Have you identified active sessions and considered long-running or uncommitted transactions?
- Have application owners and users been notified where appropriate?
- Are reconnecting applications, jobs, or monitoring checks paused if needed?
- Is your dedicated administrative query window using
master? - Have you checked
AUTO_UPDATE_STATISTICS_ASYNC? - After the task, have you restored and verified
MULTI_USERand recorded the change?
This procedure is specific to Microsoft SQL Server syntax; PostgreSQL, MySQL, Oracle, and other database systems use different mechanisms for session termination and access control.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
