How to put All User Databases to Single User Mode in SQL Server - SQL Server Tutorial

Scenario:

You are working as SQL Server DBA or Developer and you are asked to provide the scripts those can be used to put all the Database on single SQL Server Instance to Single User Mode.

Solution:

We know that we can use below query to put database into Single user mode.

Alter database [YourDatabaseName] set single_user 

with rollback immediate

To run this query for multiple databases we can use Cursor, the select query in the cursor can be modified as per your requirement. You might want to choose all the databases or databases which start with Sales%. Make change to select query to select the databases you would like to put in single user mode.

USE MASTER
GO

DECLARE @DatabaseName AS VARCHAR(128)

DECLARE Cur CURSOR
FOR
--Get list of Database those we want to put into Single User Mode
SELECT NAME
FROM sys.databases
WHERE user_access_desc = 'Multi_USER'
    AND NAME NOT IN (
        'master'
        ,'tempdb'
        ,'model'
        ,'msdb'
        )

OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    --Innser  Cursor Start
    --Kill all connection to DB before Putting into Single User Mode
    DECLARE @Spid INT

    DECLARE KillProcessCur CURSOR
    FOR
    SELECT request_session_id
    FROM sys.dm_tran_locks
    WHERE resource_database_id = DB_ID(@DatabaseName)

    OPEN KillProcessCur

    FETCH NEXT
    FROM KillProcessCur
    INTO @Spid

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @SQL VARCHAR(500) = NULL

        SET @SQL = 'Kill ' + CAST(@Spid AS VARCHAR(5))

        EXEC (@SQL)

        PRINT 'ProcessID =' + CAST(@Spid AS VARCHAR(5)) + 

' killed successfull'

        FETCH NEXT
        FROM KillProcessCur
        INTO @Spid
    END

    CLOSE KillProcessCur

    DEALLOCATE KillProcessCur

    --Inner Cursor Ends
    --Outer Cursor: Put DB in single User Mode
    DECLARE @SQLSingleUSer NVARCHAR(MAX) = NULL

    SET @SQLSingleUSer = 'ALTER DATABASE [' + @DatabaseName + '] 

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

    PRINT @SQLSingleUSer

    EXEC (@SQLSingleUSer)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
END

CLOSE Cur

DEALLOCATE Cur

--Check if all DBS are in Single Mode
SELECT NAME AS DBName
    ,state_desc
    ,user_access_desc
FROM sys.databases
WHERE user_access_desc = 'SINGLE_USER'

I executed above code on my SQL Server Instance and it was able to put the databases in single user mode.

How to put all databases in single user mode in SQL Server