How to change the ower of tables in Microsoft SQL Server database

To do this, execute the following command on every table

exec sp_changeobjectowner '?????', 'DBO' - where ????? is the name of your database table.

Also below is the stored procedure through which you can change the owner for every table inthe database.

EXEC sp_changeobjectowner @oldownerplusobject, @new

create procedure J_ChangeObjectOwner (@type varchar(1),@old varchar(20),@new

varchar(20))

as

declare @ObjectName varchar(100)

declare @oldownerplusobject varchar(50) begin declare Cursor_Object cursor for select [name] from sysobjects where type=@type and xtype=@type open Cursor_Object FETCH NEXT FROM Cursor_Object INTO @ObjectName WHILE @@FETCH_STATUS = 0 begin

set @oldownerplusobject=@old+'.'+@ObjectName

EXEC sp_changeobjectowner @oldownerplusobject, @new

print 'Permission Changed for ' + @oldownerplusobject +' to ' + @new + ' :

Process Done'

FETCH NEXT FROM Cursor_Object INTO @ObjectName end close Cursor_Object deallocate Cursor_Object end

exec J_ChangeObjectOwner 'p','xxx','yyy'

Also below is the procedure you can first create and then execute 

if exists (select * from sysobjects where id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[chObjOwner]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE proc chObjOwner( @usrName varchar(20), @newUsrName varchar(50))
as
-- @usrName is the current user
-- @newUsrName is the new user

set nocount on
declare @uid int                   -- UID of the user
declare @objName varchar(50)       -- Object name owned by user
declare @currObjName varchar(50)   -- Checks for existing object owned by new user
declare @outStr varchar(256)       -- SQL command with 'sp_changeobjectowner'
set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static
for
select name from sysobjects where uid = @uid

open chObjOwnerCur
if @@cursor_rows = 0
begin
  print 'Error: No objects owned by ' + @usrName
  close chObjOwnerCur
  deallocate chObjOwnerCur
  return 1
end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0
begin
  set @currObjName = @newUsrName + "." + @objName
  if (object_id(@currObjName) > 0)
    print 'WARNING *** ' + @currObjName + ' already exists ***'
  set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"
  print @outStr
  print 'go'
  fetch next from chObjOwnerCur into @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

then executeexec chObjOwner 'test','dbo'

the result provided can be copy pasted and then executed