Switching Database Restrict Access (User Modes)

Hi All,

This may help somewhere in the process of getting things faster.
Here you can switch all your databases from one user mode to other available in SQL server...
        a) SINGLE_USER Mode
        b) RESTRICTED_USER Mode
        c) MULTI_USER Mode.

Note: Conditions may apply to do this, just make sure before doing these. Something like below..
     i) Taking the log backup before applying this.
    ii) Run sp_who2 active to see anyone working on the database(s) and inform them to commit their transactions.. Other wise it may causes issues.


/*
what ever the mode you wanted your databases to,Just set that value below where mentioned.*/

-- I)If you wanted to change your database access as one of the following conditions Then use this script.
  /*
  1) MULTI_USER to SINGLE_USER
  2) MULTI_USER to RESTRICTED_USER
  3) SINGLE_USER to RESTRICTED_USER
  4) RESTRICTED_USER to SINGLE_USER
  */
--CODE From here

DECLARE @Mode varchar(20) =  'Usermode' --please mention one of your desired user mode here
DECLARE @DbName varchar(50)
DECLARE @SQL varchar(1024)

DECLARE UserModes CURSOR
FOR

SELECT Name FROM SYS.databases
WHERE user_access_desc <> @Mode
and database_id >4

OPEN UserModes
FETCH NEXT FROM UserModes into @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
ALTER DATABASE ['+@DbName+'] SET '+ @mode + ' WITH ROLLBACK IMMEDIATE'
FETCH NEXT FROM UserModes INTO @DbName

EXEC (@SQL)
PRINT 'Database '+@DbName+' successfully moved to ' + @Mode +' Mode'
END
CLOSE UserModes
DEALLOCATE UserModes
GO

Note : Why Rollback Immediate while altering the database?
It does rollback all the transactions currently processing with the users. Finally, It can do the altering task.

-- II) If your database in SINGLE_USER or RESTRICTED_USER mode and you wanted to change it to MULTI_USER Then use following.

DECLARE @Mode varchar(20) =  'Usermode' --please mention one of your desired user mode here
DECLARE @DbName varchar(50)
DECLARE @SQL varchar(1024)

DECLARE UserModes CURSOR
FOR

SELECT Name FROM SYS.databases
WHERE user_access_desc <> @Mode
and database_id >4

OPEN UserModes
FETCH NEXT FROM UserModes into @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '
ALTER DATABASE ['+@DbName+'] SET '+ @mode + ' WITH NO_WAIT'
FETCH NEXT FROM UserModes INTO @DbName

EXEC (@SQL)
PRINT 'Database '+@DbName+' successfully moved to ' + @Mode +' Mode'
END
CLOSE UserModes
DEALLOCATE UserModes
GO

Note : Why No_Wait while altering the database ?
It May or may not alter the database. If no one connected to that particular database It can successfully let you alter the database. Otherwise, It will fail to do altering the database.

BTW... You have another option here to set the time for the rollback...
WITH ROLLBACK AFTER 60 
Where 60  in seconds.... Basically it will wait 60 seconds to rollback the other transactions on the database and does the altering with rollback successfully.
Command looks like below:
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK AFTER 60