Disconnect all users from MS SQL Server 2005/2008 Database

To get exclusive access to the database to perform maintenance tasks etc. you can set the database to Single User Mode, which permits only one database connection at a time. At that moment if other users try to access the database while you are working on that active connection, they will receive an error.

To bring a database to the single user mode, use the following query:

ALTER DATABASE DATABASENAME SET SINGLE_USER

Users those already connected to the db when you run this command, they will not be disconnected. Instead the 'SET SINGLE_USER' command will wait till the others have disconnected.

If you want to override this scenario and forcefully disconnect other users, then use the following query:

ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

OK now your database immediately move to the single user mode.

Now After completion of your maintenance task you need to go back to multiuser mode by applying another TSQL command which is given below:

ALTER DATABASE DATABASENAME SET MULTI_USER

If you get the following error:

"Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database '___' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."

Use SP_Who (or SP_Who2 for more detailed info) to find the session ID of the active connection to the database:

EXEC sp_who

- or -

EXEC sp_who2

Terminate the connection with:

KILL [session id]

Then do the Alter Database command you tried.

Set Offline

Set Offline prevents anyone, even administrators, from re-connecting:

use master
alter database MyDatabase set offline with rollback immediate

Using SQL Server Management Studio (SQL SMS)

When you right click on a database and click Tasks and then click Detach Database, it brings up a dialog with the active connections.

By clicking on the hyperlink under "Messages" you can kill the active connections. You can then kill those connections without detaching the database.

More information on how to Kill All Processes That Have Open Connection in a SQL Server Database using SQL Server Management Studio here.