How to disconnect all users from a server ?

• SQL Guy 1

  SSCoach

  Points: 15981

  More actions

  June 1, 2010 at 9:59 am

  #141262

  Due to migration to a new server, I need to disconnect all users. I can run ALTER DATABASE <db_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE in a loop against all databases, but is there a better method for this so I don't need to write a script?

  Thanks

• Brandie Tarvin

  SSC Guru

  Points: 173105

  More actions

  June 1, 2010 at 10:24 am

  #1174898

  Better method... Not really.

  Simplier (and not recommended) methods? Yes.

  1) Set all DBs to Single User mode.

  2) Take all DBs offline (will have to click the "Disconnect connected users" option)

  3) Stop the services.

  Best method is to write your script to kill the users, then set the DBs to Single User or Offline right after each database has been killed to prevent them from logging back in.

  If you use Terminal Services or Citrix, you could kill the connections from there, but that doesn't necessarily get all users attached to the database as some could be connecting directly in.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.