EXEC CHANGEALLOBJOWNER @OLDOWNER = 'John', @newowner = 'alex'
/ * Version: SQL Server 7.0 / 2000Created by: alexander chigrikhttp: //www.mssqlcity.com/ - All About MS SQL (SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
This stored procedure can be used to run through all of a specific database's objects owned by the 'oldowner' and change the oldowner with the new one.You should pass the old owner name and the new owner name, as in the example below:
EXEC CHANGEALLOBJOWNER @OLDOWNER = 'John', @newource = 'alex' * /
IF Object_ID ('ChangeAllobjowner') Is Not Null // Line Continous Drop Proc ChangeAllobjownergo
Create Procedure ChangeAllobjowner (@Newner sysname) assdeclare @objname sysnameset nocount on
--check that the @oldowner exists in the databaseIF USER_ID (@oldowner) IS NULL BEGIN RAISERROR ( 'The @oldowner passed does not exist in the database', 16, 1) RETURN END - check that the @newowner exists in the DatabaseIF User_ID (@newowner) Is Null Begin Raiserror ('The @newowner passed does not exist in the database ", 16, 1) Return End
Declare Owner_cursor cursor for select name from sysobjects where uid = user_id (@oldowner)
OPEN owner_cursorFETCH NEXT FROM owner_cursor INTO @objnameWHILE (@@ fetch_status <> -1) BEGIN SET @objname = @oldowner '.' @Objname EXEC sp_changeobjectowner @objname, @newowner FETCH NEXT FROM owner_cursor INTO @objnameEND
Close Owner_cursordeallocate Owner_Cursorgo
