Setting All User Databases To Read Only Status

,

Every want to keep everyone from updating all your users database?  If so this script will set each database to read only status.

CREATE PROCEDURE USP_READ_ONLY_ON AS
----------------------------------------------------------------------------------------
--	                                                                          
-- WRITTEN BY: GREG LARSEN     DATE: SEPTEMBER 26, 2001                                 
-- DESCRIPTION: THIS PROCESS SETS ALL DATABASE TO READ ONLY STATUS

----------------------------------------------------------------------------------------
--
-- GET THE NAME OF ALL DATABASES
--
DECLARE ALLDATABASES CURSOR FOR

SELECT NAME FROM MASTER..SYSDATABASES WHERE NAME NOT IN ('MASTER', 'TEMPDB','MODEL','MSDB') 
  
OPEN ALLDATABASES
  
DECLARE @DB NVARCHAR(250) 
FETCH NEXT FROM ALLDATABASES INTO @DB

WHILE (@@FETCH_STATUS = 0)

BEGIN
  PRINT 'SETTING DB=' +RTRIM(@DB) + ' TO READ ONLY.'
  FETCH NEXT FROM ALLDATABASES INTO @DB
EXEC MASTER..SP_DBOPTION @DB, 'READ ONLY', 'TRUE'
END -- WHILE (@@FETCH_STATUS = 0)

CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES

Rate

Share

Share

Rate