sql-server - SQL Server 2016 更改对象所有者

Question

我继承了一个 SQL 2008 数据库，其中它的所有对象都以作为所有者的开发人员名称为前缀，即 ownername.sp_get_all_users。

我已将数据库恢复到 SQL Server 2016 Express Edition。

有数百个 dbase 对象，有没有办法自动将对象所有者更改为 dbo 而不是手动编辑每个对象？

我尝试了以下方法，但显然自 SQL Server 2005 以来您不能再对对象进行临时更改？

SELECT * from sysobjects where uid = user_id('UseNAme')
declare @Return int
exec @Return = sp_configure 'allow updates', '1'
SELECT @Return as 'Returned Code'
GO
reconfigure WITH OVERRIDE
GO
DECLARE @Rows int, @Error int
BEGIN TRANSACTION
update sysobjects set uid = user_id('dbo') where uid = user_id('UseNAme')
SELECT @Error = @@Error, @Rows = @@RowCount
SELECT @Rows as '#Rows'
IF @Rows > 0
 BEGIN  
  SELECT  @Rows AS '#Rows'
  COMMIT TRANSACTION
 END
else 
 BEGIN
  SELECT @Error AS 'Error #'
  ROLLBACK TRANSACTION
 END

exec sp_configure 'allow updates', '0'
reconfigure WITH OVERRIDE
go

非常感谢任何帮助。

Answer 1

您必须使用 Alter Schema...

ALTER SCHEMA oldschemaname TRANSFER dbo.Address; 

下面要自动化使用

this will change all tables which have a schema other than system to dbo,note if you have two tables in different schema,they can't exist in same schema

select *,row_number() over (order by (select null)) as rownum
into #tables
 from information_Schema.tables
where table_schema in (select name  from sys.schemas
 where name  not in ('dbo','guest','INFORMATION_SCHEMA','sys') and principal_id <16384
 )

now move 
declare @min int,@max int

select @min=min(rownum),@max=max(rownum)
from #tables

declare @tblname varchar(255),@schemaname sysname
declare @sql varchar(max)

while @min<=@max
Begin

select @tblname=table_name,@schemaname=table_schema from
#tables where rownum=@min

set @sql='alter schema dbo transfer '+ @schemaname+'.'+@tblname

--print @sql
exec(@sql)

Set @min=@min+1
End

sp_change object owner as per documentation states..

This stored procedure only works with the objects available in MicrosoftSQL Server 2000. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SCHEMA or ALTER AUTHORIZATION instead. sp_changeobjectowner changes both the schema and the owner. To preserve compatibility with earlier versions of SQL Server, this stored procedure will only change object owners when both the current owner and the new owner own schemas that have the same name as their database user names.