HOWTO: Change Objects to DBO

When restoring a MS SQL database from another server sometimes the tables/objects will be owned by the previous user on the old server. To change ownership on all tables/objects to DBO (the user you assigned to the database and gave DBO permissions) run the following query on the database (see below for last step after running query):

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

After running the above query you then need to execute the following against the database from query analyzer: ChangeObjOwners

This is particularly useful when using applications such as Kentico or Umbraco, since both assume that the objects in the database will use the dbo schema.