| Home |
| Articles |
| Backup |
| Books |
| Certification |
| FAQ |
| Products |
| Replication |
| Scripts |
| Seminars |
| Training |
| TSQL |
|
| MSDN Fourms |
| Philippine SSUG |
| Fort Worth SSUG |
| Oklahoma City SSDG |
|
| Resume |
|
| MHS Enterprises |
| BlowFrog Software |
| FilAm Software |
| AcrylicAcetate.com |
| Bargain Humidors |
| Western Humidor |
|
|
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 |
