Change all object owners

This tip shows you how to change the object owner.

This batch job changes the object owner where the uid = UseNAme:

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

For More Information


This was first published in February 2005

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close