How do I Change all objects in a database to DBO?

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. 

Add Feedback