Wednesday, March 25, 2009

Change the owner of object in SQL Server 2000

UPDATE [dbname].dbo.sysobjects SET uid=6 WHERE uid = 1

To get user id:
select DISTINCT o.name, o.uid from dbo.sysusers o left join master.dbo.syslogins l on l.sid = o.sid where ((o.issqlrole != 1 and o.isapprole != 1 and o.status != 0) or (o.sid = 0x00) and o.hasdbaccess = 1)and o.isaliased != 1 order by o.name

If it throws the error 
'Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.'

Then change the configuration
exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
and then try again.

No comments:

Post a Comment