Technical Article

Set all databses to dbo.

,

I got this script from this site and it works fantastic. I would just like to give an update to this script. All credit should go to the person that published this script on this site not me. I would just like to point out a few things. If you want to schedule this as a job once a new databse get created you should include the msdb, model, northwind, pubs, master and tempdb in the script to not be set to dbo or single user otherwise your sql server agent wont start. The databses can not be set to dbo and single at the same time. I made it to only set the database to dbo and to exclude the above mentioned database. PLEASE NOTE THIS IS NOT MY WORK ALL THE CREDIT SHOULD GOT TO GREG LARSEN FOR THIS GREATE SCRIPT.

SET NOCOUNT ON

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

SELECT name FROM master..sysdatabases
   where name not in ('master','tempdb', 'model', 'msdb', 'Northwind', 'pubs')

-- 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)

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

END

-- Close and Deallocate Cursor
CLOSE AllDatabases
DEALLOCATE AllDatabases

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating