What SQL is required to put SQL Server 2005 in single user mode?

Question

I would like to know how (and by extension if it's possible) to put SQL Server 2005 in single user mode using SQL statements?

I found these instructions on the MSDN, but alas they require SSMS.

http://msdn.microsoft.com/en-us/library/ms345598(SQL.90,loband).aspx

*To set a database to single-user mode

1. In Object Explorer, connect to an instance of the SQL Server 2005 Database Engine, and then expand that instance.
2. Right-click the database to change, and then click Properties.
3. In the Database Properties dialog box, click the Options page.
4. From the Restrict Access option, select Single.
5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.*

Answer 1

The following should work:

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH NO_WAIT
GO

with

ALTER DATABASE [MyDatabase] SET MULTI_USER WITH NO_WAIT
GO

to set it back to multi-user

Answer 2

On a per-database basis, you can set single user mode with:

ALTER DATABASE database_name SET SINGLE_USER

If other users are logged in and using the database though, then you'll have to kill their spids first otherwise this will fail. You could write a script that basically SELECTs from sysprocesses and issues a KILL command for each spid listed against your target dbid.

Answer 3

See msdn

alter database X
set single_user

Answer 4

Try

alter database adventureWorks set SINGLE_USER with rollback immediate

Should you wish to provide ample time for already executing transactions to complete gracefully you can issue the following:

alter database adventureWorks set SINGLE_USER with rollback after 60 seconds

Answer 5

i found these commands:

ALTER DATABASE [dbName] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [dbName] SET SINGLE_USER WITH NO_WAIT

or

EXEC sp_dboption 'dbName', 'single user', 'false'

EXEC sp_dboption 'dbName', 'single user', 'true'

at:

http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx

Give them a try.