Step 1
You will need to create the following stored procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
CREATE PROC dbo.ChangeObjOwners AS SET NOCOUNT ON DECLARE @dynsql VARCHAR(1000) SET @dynsql = '' DECLARE @Obj_Owner sysname SET @Obj_Owner = '' DECLARE @Obj_Type VARCHAR(30) SET @Obj_Type = '' DECLARE @Obj_Name sysname SET @Obj_Name = '' DECLARE @ObjCounter INT SET @ObjCounter = 0 DECLARE @DBO CHAR(3) SET @DBO = 'DBO' -- temp table to hold all objects not owned -- by DBO CREATE TABLE #ChangeOwners( id INT identity(1,1), Obj_Owner sysname, Obj_Name sysname, Obj_Type VARCHAR(30)) -- populate it INSERT #ChangeOwners (Obj_Owner, Obj_Name, Obj_Type) SELECT su.name, so.name, CASE WHEN type = 'u' THEN 'table' WHEN type = 'p' THEN 'sproc' WHEN type = 'v' THEN 'view' END AS obj_type FROM sysusers su JOIN sysobjects so ON su.uid = so.uid WHERE su.name NOT IN ('information_schema', 'dbo') AND so.type IN ('p', 'u', 'v') -- select * from #ChangeOwners SET @ObjCounter = @@rowcount -- holds the count of rows inserted into #ChangeOwners WHILE @Objcounter > 0 BEGIN -- construct string for object ownership change SELECT @Obj_Name = Obj_Owner + '.' + Obj_Name FROM #ChangeOwners WHERE id = @ObjCounter SELECT @Obj_Type = Obj_Type FROM #ChangeOwners WHERE id = @ObjCounter SET @dynsql = 'sp_ChangeObjectOwner ''' + @Obj_Name + ''', ' + @DBO --select @dynsql PRINT 'changing ownership on ' + @Obj_Type + ': ' + @Obj_Name EXEC(@dynsql) SET @ObjCounter = @ObjCounter - 1 END -- ok all done, collect garbage DROP TABLE #ChangeOwners GO |
Step 2
Once this is done executing all you simply have to do is run it against your Database: ChangeObjOwners
This will change all the objects on the Database (Tables, SPs, Views) to DBO.
Content retrieved from: https://support.appliedi.net/kb/a289/how-do-i-change-all-objects-in-a-database-to-dbo.aspx.