GDI Lord (3/18/2010)
Users can be an interesting bunch after all
Amen.
I was going to do it via a for loop in the app, merely KILLing all session ids that are not the current user's and that are connected to the target database. However, is there a better way?
The correct syntax is:
-- Kick everyone out, rolling back
-- their transactions if necessary
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
--
-- Do admin stuff
--
-- Restore access to the database
ALTER DATABASE AdventureWorks
SET MULTI_USER;
Notice the lack of an underscore between ROLLBACK and IMMEDIATE 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi