One of the things that really troubles me is having to take over a server that has not been properly maintained previously, and one of the biggest headaches tends to be object owned by different users.

In order to help remove problems with ownership chains it is always better to have dbo own the objects. Books online tell you that you can't change the owner of the objects, therefore making the taks very arduous - especially if they are multi-million row tables.

I have therefore amended the SQL Server 7.0 sp_changeobjectowner to work on 6.5. Just run the script below into the master database.

Be careful, however, to heed the warnings. Careful thought must be used before changing object ownership, and always take a backup first!!!

<<SCRIPT BEGIN>>

if exists (select * from sysobjects where id = object_id(&#39;dbo.sp_changeobjectowner&#39 and sysstat & 0xf = 4)
drop procedure dbo.sp_changeobjectowner
GO

/*
Procedure sp_changeobjectowner
John Thorpe 1999

For SQL Server version 6.x only

Changes owner of object supplied to new name, also supplied.

Object must be one of: User Table, View, or Stored Procedure, and new owner must
already exist in the database. Usage restricted to sa/dbo only.

*/

create procedure sp_changeobjectowner
@objname varchar(64), -- may be &#34;[owner].[object]&#34;
@newowner varchar(32) -- must be entry from sysusers
as
set nocount on
declare @objid int,
@newuid smallint

-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:
-- (1) &#39;sa&#39;
-- (2) &#39;dbo&#39;
-- (3) Aliased as &#39;dbo&#39;
if suser_id() <> 1 and user_id() <> 1
and (select isnull(altsuid,0) from sysalternates where suid = suser_id()) <> 1
begin
raiserror(15000,-1,-1)
return (1)
end

-- RESOLVE OBJECT NAME (MUST BE USER TABLE, VIEW OR STORED PROCEDURE) --
select @objid = object_id(@objname)
if (@objid is null) OR NOT EXISTS (select type from sysobjects where id = @objid and type in (&#39;U&#39;,&#39;V&#39;,&#39;P&#39)
begin
-- OBJECT NOT FOUND
raiserror(15001,-1,-1,@objname)
return 1
end

-- RESOLVE NEW OWNER NAME --
-- Public cannot own objects --
select @newuid = uid from sysusers where name = @newowner
and name <> &#39;public&#39;

if @newuid is null
begin
raiserror(15008, -1, -1, @newowner)
return (1)
end

-- DO THE OWNER TRANSFER (WITH A WARNING) --
raiserror(&#39;Caution: Changing any part of an object name could break scripts and stored procedures.&#39;,-1,-1)
raiserror(&#39;Warning: Permissions on the changed object will be dropped and must be reset.&#39;,-1,-1)

begin transaction
-- drop permissions (they&#39;ll be incorrect with new owner) --
delete sysprotects where id = @objid
-- change owner --
update sysobjects set uid = @newuid where id = @objid
commit transaction

-- SUCCESS MESSAGE --
raiserror(&#39;The object owner has been changed&#39;,-1,-1)
return 0
GO

GRANT EXECUTE ON dbo.sp_changeobjectowner TO public
GO

<<SCRIPT END>>