ChangeAllObjectOwner
发布时间：2018-08-18 14:11:17 所属栏目：运营 来源：站长网
导读：EXEC ChangeAllObjOwner @oldowner = 'John', @newowner = 'Alex' /* 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 store
|
EXEC ChangeAllObjOwner @oldowner = 'John', @newowner = 'Alex' /* 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 //line continous 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 （编辑：广西网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐