Robert klimes (2/9/2011)

---

You could try creating a central management server(http://msdn.microsoft.com/en-us/library/bb934126.aspx) an then use

sp_msforeachdb 'USE [?]; EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = false'

sp_msforeachdb 'ALTER AUTHORISATION DATABASE [?] TO sa' 

DHeath (2/9/2011)

---

Ok update.... have tried to use the code snippit of

EXEC sp_MSforeachdb 'USE ?; EXEC sp_changedbowner ''sa'''

and running that on the master database but it errors out with

Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.
Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.
Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.

i have tried on SQL 2008 and 2005 with both returning the same error messages...any ideas? Also this also errors out if its ran on a user database with the same errors.

DHeath

Use MASTER
GO
DECLARE @sqlBase nvarchar(300) = 'ALTER AUTHORIZATION DATABASE XXX TO SA'+char(13)+char(10)
DECLARE @sql nvarchar(max)=''
SELECT @sql = @sql+'REPLACE(sqlBase,''XXX'',name) FROM sys.databases'
  WHERE name NOT IN ('MASTER','MODEL','TEMPDB','DISTRIBUTION')
EXEC (@sql)