Change table owner

This is more a MSSQL-tips, but it can be usefull for Axapta when you attach a existing database to your MSSQL-server. Sometimes the user of the attached database has the same name as the one already declared in MSSQL-server. Then use this code to generate another code who change the ownershop of each table in the attached database:

<xpp> SELECT 'exec sp_changeobjectowner + ltrim(u.name) + '.' + ltrim(s.name) + ,' + dbo FROM sysobjects s,sysusers u WHERE s.uid = u.uid AND u.name <> 'dbo' AND xtype in ('V', 'P', 'U') AND u.name not like 'INFORMATION%' order by s.name </xpp>

This scripts do actual update (taken from mazzy.ru): <xpp> declare @@name nvarchar(517)

declare tables cursor for select

      u.name + '.' + o.name

from

      sysobjects o inner join
      sysusers u on (o.uid = u.uid)

where

      type in ('U', 'V') and u.name = 'bmssa'
                               -- Name ^^^^^ sholuld be replaced

open tables

fetch from tables into @@name while @@fetch_status = 0 begin

     exec sp_changeobjectowner @@name, 'dbo'
     fetch from tables into @@name

end

close tables deallocate tables </xpp> --CODEWIRE.NET 06:36, 12 March 2007 (EDT)