Set database from SINGLE USER mode to MULTI USER

The “user is currently connected to it” might be SQL Server Management Studio window itself. Try selecting the master database and running the ALTER query again.

That error message generally means there are other processes connected to the DB. Try running this to see which are connected:

exec sp_who

That will return you the process and then you should be able to run:

kill [XXX]

Where [xxx] is the spid of the process you're trying to kill.

Then you can run your above statement.

Good luck.

You can add the option to rollback your change immediately.

ALTER DATABASE BARDABARD
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO

SQL Server 2012:

right-click on the DB > Properties > Options > [Scroll down] State > RestrictAccess > select Multi_user and click OK.

Voila!

I had the same issue and it fixed by the following steps - reference: http://giladka8.blogspot.com.au/2011/11/database-is-in-single-user-mode-and.html

use master
GO

select 
    d.name, 
    d.dbid, 
    spid, 
    login_time, 
    nt_domain, 
    nt_username, 
    loginame
from sysprocesses p 
    inner join sysdatabases d 
        on p.dbid = d.dbid
where d.name = 'dbname'
GO

kill 56 --=> kill the number in spid field
GO

exec sp_dboption 'dbname', 'single user', 'FALSE'
GO