axaptapedia: Change table owner

[Blog bot]

Источник: http://www.axaptapedia.com/Change_table_owner
==============

Summary:
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:


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


This scripts do actual update:

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

--[[User:Codewire.net|CODEWIRE.NET]] 06:36, 12 March 2007 (EDT)


Источник: http://www.axaptapedia.com/Change_table_owner

[glibs]

На Technet как-то давно предлагали более изящный способ

sp_msforeachtable "sp_changeobjectowner '?', 'dbo'"

(этот текст нужно набрать и запустить в Query Analyser, выбрав нужную базу).