mssqlserver.com: FAQ: How can I change the owner of an object?

How can I change the owner of an object?

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.

Get the uid number for the new owner from sysusers
( select name, uid from sysusers where name = "<New Owner Name>" )
Configure your server to allow updates to system tables
( exec sp_configure "allow updates",1 )
( reconfigure with override )
Begin a transaction
Update the sysobjects table, changing the uid column value of the objects concerned to the uid you want.
( update sysobjects set uid = <New UID> where name = "<Table Name>" )
Check that the right number of rows have been affected.
Commit or rollback the transaction, depending on the result
Configure your server to NOT allow updates to system tables
( exec sp_configure "allow updates",0 )
( reconfigure with override )
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

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.