Turn off DBO and Single User mode for all DB's

,

Here is a companion script to "Set All databases to DBO only and Single User Mode" that will turn off dbo and single user mode for all databases.

CREATE PROCEDURE turnoff_dbo_only_single_user_alldb AS
----------------------------------------------------------------------------------------
--	                                                                              -- 
-- WRITTEN BY: GREG LARSEN     DATE: NOVEMBER 1, 2000                                 --
-- DESCRIPTION: This stored procedure will put all databases in dbo only 
--              and single user mode.
----------------------------------------------------------------------------------------

SET NOCOUNT ON

--
-- Get the name of all databases
--
DECLARE AllDatabases CURSOR FOR

SELECT name FROM master..sysdatabases 
  

OPEN AllDatabases

  

DECLARE @DB NVARCHAR(128)
DECLARE @COMMAND NVARCHAR(128) 

FETCH NEXT FROM AllDatabases INTO @DB

WHILE (@@FETCH_STATUS = 0)

BEGIN

    	set @command ='master..sp_dboption @dbname=''' + @db + ''',@optname=''DBO USE ONLY'', @optvalue=''FALSE'''
   	print @command
   	exec (@command)
   	set @command = 'master..sp_dboption @dbname=''' + @db + ''',@optname=''single user'', @optvalue=''FALSE'''
  	print @command
  	exec (@command)
  	FETCH NEXT FROM AllDatabases INTO @DB

END



  

   CLOSE AllDatabases

   DEALLOCATE AllDatabases





GO

Rate

Share

Share

Rate