一次性更该所有对象所有者的存储过程。-CSDN博客

有时候以不同的用户连接sql server 2000数据库,创建的对象就不一样，这是如果要删除某个用户，则需要把他所拥有的所有对象的所有者更该掉。

本存储过程就是用来一次性更该所有对象的所有者的。

[@more@]

/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://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 old
owner 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', @newowner = 'Alex'
*/

IF OBJECT_ID('ChangeAllObjOwner') IS NOT NULL DROP PROC ChangeAllObjOwner
GO

CREATE PROCEDURE ChangeAllObjOwner (
@oldowner sysname,
@newowner sysname
)
AS
DECLARE @objname sysname
SET NOCOUNT ON

--check that the @oldowner exists in the database
IF 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 database
IF 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_cursor
FETCH NEXT FROM owner_cursor INTO @objname
WHILE (@@fetch_status <> -1)
BEGIN
SET @objname = @oldowner + '.' + @objname
EXEC sp_changeobjectowner @objname, @newowner
FETCH NEXT FROM owner_cursor INTO @objname
END

CLOSE owner_cursor
DEALLOCATE owner_cursor
GO

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53005/viewspace-779312/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53005/viewspace-779312/