- Function: batch change the database owner objects - Author: Unknown - Usage: exec ChangeObjectOwner 'nmkspro', 'dbo' - can be changed to target all nmkspro owner dbo all - run success The post will be prompted: "Note: All parts of the object name may destroy the script and stored procedures." Create Procedure dbo.changeObjectowner @oldowner as nvarchar (128), - Parameter original owner @newowner as nvarchar (128) - Parameter new owner AS
Declare @name as nvarchar (128) Declare @owner as nvarchar (128) Declare @Ownername as nvarchar (128)
DECLE CUROBJECT CURSOR for SELECT 'Name' = Name, 'Owner' = User_name (UID) from sysobjects where user_name (uid) = @ Oldowner Order by Name
OPEN curObjectFETCH NEXT FROM curObject INTO @Name, @OwnerWHILE (@@ FETCH_STATUS = 0) BEGIN if @ Owner = @ OldOwner begin set @OwnerName = @OldOwner '.' Rtrim (@Name) exec sp_changeobjectowner @OwnerName, @NewOwner end
Fetch next from curobject @Name, @Ownerend
Close Curobjectdeallocate CurobjectGO
