Ms SQL Server Stored Procedure batch modify user table owners

Ms SQL Server Stored Procedure batch modify user table owners

Create a login account and set login to the database to be modified.

Create storage process

Create procedure DBO. changename
@ Oldowner as nvarchar (128), -- original parameter owner
@ Newowner as nvarchar (128) -- New Parameter owner
As

Declare @ name as nvarchar (128)
Declare @ owner as nvarchar (128)
Declare @ ownername as nvarchar (128)

Declare curobject cursor
Select 'name' = Name,
'Owner' = user_name (UID)
From sysobjects
Where user_name (UID) = @ oldowner
Order by name

Open curobject
Fetch next from curobject into @ name, @ owner
While (@ fetch_status = 0)
Begin
If @ owner = @ oldowner
Begin
Set @ ownername = @ oldowner + '.' + rtrim (@ name)
Exec sp_changeobjectowner @ ownername, @ newowner
End

Fetch next from curobject into @ name, @ owner
End

Close curobject
Deallocate curobject
Go

Call method: exec changename 'oldowner ', 'newowner'

Note: This method produces many stored errors.