If you set a Database in Single User Mode and you loose you conection to the session that you set single user mode what do you do to continue your work id you loose your connection? That is what if you accedendlty close you SPID?

If someone has the sole connection I would not be able to kill their session?

Worst case scenario would resrting the SQL Server Service start?

If you've set a particular DATABASE to single user mode, ie., , you should still be able to connect to the SERVER and have your connection set to another database, like for instance MASTER which is probably the default database associated with your login. The one exception I seem to notice that if in SSMS I set my DEFAULT database (as speced in SSMS properties for my "login") to the same database that I for whatever reason set to single user and THEN LOST MY CONNECTION, then yes I was pretty much locked out with SSMS. So you'd probably not want to set your database to SINGLE_USER if it is the DEFAULT DATABASE that your login is associated with.

So thats an interesting situation that I wasn't aware of.

Trying Lowells solution I was able to log back in with:

ALTER DATABASE TEST SET ONLINE

BUT ONLY if I added:

ALTER DATABASE TEST SET MULTI_USER

---> USING MY EXISTING CONNECTION THAT I HAD OPEN <---

If you've set your SERVER to single user mode, ie., startup parameter -m, that looks to me to be a different situation.

patrickmcginnis59 10839 (5/6/2013)The one exception I seem to notice that if in SSMS I set my DEFAULT database (as speced in SSMS properties for my "login") to the same database that I for whatever reason set to single user and THEN LOST MY CONNECTION, then yes I was pretty much locked out with SSMS. So you'd probably not want to set your database to SINGLE_USER if it is the DEFAULT DATABASE that your login is associated with.

Click the 'options' button on the login dialog, enter a different database name in the 'Connect to database' drop down, that overrides the default database. I suggest using master.