T-Sql script to make all Databases to Read-only mode in sql server 2012

_{Thursday, September 12, 2013 6:32 PM}

Hi All,

I am trying to make few databases to Read-only .Once testing is done I need to switch back to read/wrire mode.I am looking forT-Sql script to make all Databases to Read-only mode in sql server 2012.Colud you please give me a T-SQl script instead of GUI

_{Thursday, September 12, 2013 6:44 PM ✅Answered | 1 vote}

Hi,

Try like this ,

SELECT  name,iDENTITY(INT) n INTO ##temp FROM Sys.sysdatabases WHERE dbid NOT IN (1,2,3,4)DECLARE @i INT = 1,@DBNAME VARCHAR(20)WHILE @i <= (SELECT MAX(n) FROM ##temp)BEGINSELECT @DBNAME = name FROM ##temp WHERE n = @iEXEC ('ALTER DATABASE '+ @DBNAME +' SET READ_ONLY')SET @i = @i + 1ENDDROP TABLE ##temp

To set database to read-write mode :

ALTER DATABASE [DBNAME] SET READ_WRITE 

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

_{Monday, September 16, 2013 2:06 PM}

Databases need to be in single_user in order to make them readonly.  This would not work if anyone is accessing the databases.  Need to add alter to single_user to the script.