How can I change the owner of an object?

A. With SQL 7.0 there is a stored-procedure (sp_changeobjectowner) to do this, however under SQL 6.5 and earlier there is no supported method to do this. (With 7.0 the sp doesn't work for user-defined types so you'll have to use the direct method).

It can be achieved by directly addressing and updating the system table concerned though.

1. Get the uid number for the new owner from sysusers
( select name, uid from sysusers where name = "" )
2. Configure your server to allow updates to system tables
( exec sp_configure "allow updates",1 )
( reconfigure with override )
3. Begin a transaction
4. Update the sysobjects table, changing the uid column value of the objects concerned to the uid you want.
( update sysobjects set uid = where name = "" )
5. Check that the right number of rows have been affected.
6. Commit or rollback the transaction, depending on the result
7. Configure your server to NOT allow updates to system tables
( exec sp_configure "allow updates",0 )
( reconfigure with override )
8. Stop and start your SQL Server

(The last step is necessary as portions of system tables are kept in memory by SQL Server, and the only way to force these to update is to recycle SQL Server)

as an example :-

select * from sysobjects where uid = user_id('user1')

exec sp_configure 'allow updates', '1'

reconfigure WITH OVERRIDE

go

BEGIN TRANSACTION

update systypes set uid = user_id('dbo') where uid = user_id('user1')

COMMIT TRANSACTION

exec sp_configure 'allow updates', '0'

reconfigure WITH OVERRIDE

go