Set All databases to DBO only and Single User Mode

,

Every want an easy way to set all database to DBO only and single user mode?  Well here is a script to do that for you. This script is useful when you want to keep users out of the all the databases.

----------------------------------------------------------------------------------------
-- WRITTEN BY: GREG LARSEN DATE: NOVEMBBER 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
   where name not in ('master','tempdb')

-- Open Cursor
OPEN AllDatabases

-- Define variables needed
DECLARE @DB NVARCHAR(128)
DECLARE @COMMAND NVARCHAR(128)

-- Get First database
FETCH NEXT FROM AllDatabases INTO @DB

-- Process until no more databases
WHILE (@@FETCH_STATUS = 0)
BEGIN

-- Build command to put database into DDBO ONLY mode
  set @command ='master..sp_dboption @dbname=''' + @db +
                ''',@optname=''DBO USE ONLY'', @optvalue=''TRUE'''

-- Print command to be processed
  print @command

-- Process Command
  exec (@command)

-- Set the command to set database to ssingle user mode
  set @command = 'master..sp_dboption @dbname=''' + @db +
                ''',@optname=''single user'', @optvalue=''TRUE'''

-- Print command to be processed
  print @command

-- Process command
  exec (@command)

-- Get next database
  FETCH NEXT FROM AllDatabases INTO @DB

END

-- Close and Deallocate Cursor
CLOSE AllDatabases
DEALLOCATE AllDatabases

Rate

Share

Share

Rate