T-SQL: Quickly Kick All Users Out of a SQL Server Database

Posted on January 30, 2007 by

Chrissy LeMaire — 5 Comments ↓

There are a few ways to do this but here’s one that I like. I believe it works in SQL Server 7, 2000 and 2005.

ALTER DATABASE myDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE myDatabaseName
GO

Go ahead and do what you need to do then set it back:

ALTER DATABASE myDatabaseName SET MULTI_USER

About Chrissy LeMaire

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server