Change the current owner of the SQL Server Object to the target owner.

In database operations, you will use objectowner when the Database Object List contains more than one common element, objectname. objectname to reference objects, but if you need to change the owner of objects in the current database, you can use the system stored procedure sp_changeobjectowner (Click here for more details)

Sp_changeobjectowner
Change the owner of objects in the current database.
Syntax: sp_changeobjectowner [@ objname =] 'object', [, @ newowner =] 'owner'. Parameter. [@ objname =] 'object '.

However, because there are too many database objects and you want to modify the owner of objects in the current database through batch processing, you can try to use the nf_ChangeObjectOwner Stored Procedure of Net Fetch to complete batch processing. The usage is as follows:

First, use the following code to create a stored procedure --

Nf_ChangeObjectOwner
Change the current owner of the SQL Server Object to the target owner.
Syntax: nf_ChangeObjectOwner [, @ current_Owner =] 'owner', [, @ target_Owner =] 'owner', [, @ modify_Type =] type
Copy codeThe Code is as follows:
If exists (select * from sysobjects where id = object_id (n' [nf_ChangeObjectOwner] ') and OBJECTPROPERTY (id, n'isprocedure') = 1)
Drop procedure nf_ChangeObjectOwner
GO

Create PROCEDURE nf_ChangeObjectOwner
@ Current_Owner nvarchar (255 ),
@ Target_Owner nvarchar (255 ),
@ Modify_Type int
/*************************************** **************************************** ****************

Nf_ChangeObjectOwner

Function: change the current owner of the SQL Server Object to the target owner.
* *** Back up the database before using this code!
* ** The security issues have nothing to do with the donkey of the house!
* *** Welcome to the ax!
Call method: Exec nf_ChangeObjectOwner @ current_Owner, @ target_Owner, @ modify_Type

Input parameter: @ current_Owner nvarchar (255) -- current owner of the object
@ Target_Owner nvarchar (255) -- target owner of the object
@ Modify_Type int -- 0 is the default value. Change the table owner. 1 is the view and stored procedure.
Output parameter: RETURN value =-1 -- operation object is 0, operation object does not exist
=-2 -- operation failed, the object may be locked
= 0 (default) -- operation successful, print the number of changed objects @ object_Num
@ Object_Num -- SQL print value, returns the number of successfully modified objects

@ Write by Net Fetch. @ At 2005/09/12
@ Email: cnNetFetch * Gmail. Com blog.ad0.cn

**************************************** **************************************** ****************/
AS
DECLARE @ str_Tbl_Name nvarchar (255), @ object_Num int, @ current_Owner_uid smallint
Set @ object_Num = 0
DECLARE @ return_status int
Set @ return_status =-1
Set @ current_Owner_uid = (Select uid From sysusers Where [Name] = @ current_Owner)
If Not (Len (@ current_Owner_uid)> 0)
RETURN-1
If (@ modify_Type = 1)
DECLARE ChangeObjectOwner_Cursor cursor for Select [Name] From sysobjects Where (type = 'U' or type = 'V' or type = 'P ') and userstat = 0 and [Name] <> 'nf _ ChangeObjectOwner 'and status>-1 and uid = @ current_Owner_uid
Else
DECLARE ChangeObjectOwner_Cursor cursor for Select [Name] From sysobjects Where (type = 'U' or type = 'V' or type = 'P ') and userstat <> 0 and [Name] <> 'dtproperties' and uid = @ current_Owner_uid
OPEN ChangeObjectOwner_Cursor
Begin transaction Change_ObjectOwner
Fetch next from ChangeObjectOwner_Cursor INTO @ str_Tbl_Name
WHILE (@ FETCH_STATUS = 0)
BEGIN
Set @ str_Tbl_Name = @ current_Owner + '.' + @ str_Tbl_Name
Print @ str_Tbl_Name
EXEC @ return_status = sp_changeobjectowner @ str_Tbl_Name, @ target_Owner
IF (@ return_status <> 0)
BEGIN
Rollback transaction Change_ObjectOwner
RETURN-2
END
Set @ object_Num = @ object_Num + 1
Fetch next from ChangeObjectOwner_Cursor INTO @ str_Tbl_Name
END
Print @ object_Num
Commit transaction Change_ObjectOwner
CLOSE ChangeObjectOwner_Cursor
DEALLOCATE ChangeObjectOwner_Cursor

Go

Usage (Usage): Exec nf_ChangeObjectOwner 'dbo', 'Your username', 0
-- Change the owner of all user tables to 'Your username'
Usage (Usage): Exec nf_ChangeObjectOwner 'dbo', 'Your username', 1
-- Change the owner of database objects (views and stored procedures) except user tables to 'Your username'