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 in the database.
EXEC sp_changeobjectowner @oldownerplusobject, @new
create procedure _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 _ChangeObjectOwner 'p','xxx','yyy'