Change the current owner of the SQL Server object to the target owner _mssql

In database operations, you will use Objectowner.objectname to refer to objects when the list of database objects does not have a common element--objectname, but you can use system stored procedures if you need to change the owner of the objects in the current database sp_ Changeobjectowner (click here for more details)

Sp_changeobjectowner
Changes the owner of an object in the current database.
Syntax: sp_changeobjectowner [@objname =] ' object ', [, @newowner =] ' owner '. Parameters. [@objname =] ' Object '.

But often because there are too many database objects and you want the batch to change the owner of the objects in the current database, you can try to use the Nf_changeobjectowner stored procedure of net fetch to complete the batch process. The specific usage is as follows:

First use the following code to create the 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 Code code 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

Implementation function: Change the current owner of the SQL Server object to the target owner
Please back up the database before using this code!
The safety problems brought by the donkey have nothing to do with my family!
Welcome ax to chop me, split the dead idol!
Call method: Exec Nf_changeobjectowner @current_Owner, @target_Owner, @modify_Type

Input parameters: @current_Owner nvarchar (255)--the current owner of the object
@target_Owner nvarchar (255)--target owner of the object
@modify_Type int--0 to default, change the owner of the table; 1 for views and stored procedures
Output parameter: Return value =-1--The Action object is 0, the action object does not exist
=-2--operation failed, possible object locked
= 0 (default)--successful operation, number of changed objects @object_num
@object_Num--sql Print value, returns the number of objects that changed successfully

@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 use Rstat=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 use Rstat<>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 (using method): Exec nf_changeobjectowner ' dbo ', ' your username ', 0
--Change the owner of all user tables to ' your username '
Usage (using method): Exec nf_changeobjectowner ' dbo ', ' Your username ', 1
-Change the owner of a database object (view, stored procedure) other than the user table to ' your username '