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

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

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, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server
5 comments on “T-SQL: Quickly Kick All Users Out of a SQL Server Database
  1. ked says:

    Ah thanks! Worked well enough on sqlsrv2k.

    Saw that noone left any comment so I’ll just leave this here :)

    /K

  2. Toby says:

    Thanks, this is exactly what I was looking for!

  3. mlebel says:

    This is a great and easy way ! Thanks a lot

  4. Ken says:

    There's a slight danger to doing this if you have a persistent connection. It is unlikely, although possible that another connection might grab the single user connection, effectively locking you out of the database. If that happens, there's a simple way to steal it back.

    Run:
    use MyDatabaseName
    go 1000

    and then go kill the offending session or sessions. Sooner or later, your query will grab the connection.

    Cheers.

Leave a Reply

Your email address will not be published. Required fields are marked *

*