DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to put a database into single-user mode?

The following is the Microsoft SQL Server T-SQL command for it:

ALTER DATABASE pubs SET SINGLE_USER

Stop SQL Server Agent prior, otherwise may take the only connection and lock you (sysadmin) out.

If there are still users, they can use the database for the current session. Of course, you can kill the users' spid-s to force them out. To rollback incomplete transaction, use:

ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTRICTED_USER provides for only members of the db_owner fixed database role, dbcreator and sysadmin fixed server roles to connect to the database in one or more connections:

ALTER DATABASE pubs SET RESTRICTED_USER

To return to normal multi-user state from single-user mode:

ALTER DATABASE pubs SET MULTI_USER

If you get Error Msg 5064, that means there is a live connection to the database. Make sure you are not connected to it in a Query Editor session. If it shows in the Available Databases window, reposition to tempdb or any other db. Otherwise, you can locate the connecting session by exec sp_who and use kill <SPID> to terminate the connection.

 

Related article:

How to: Set a Database to Single-user Mode (SQL Server Management Studio)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE