更改数据库所有者代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】更改数据库所有者

作者:Dezai.CN / 发布于2011/7/11/ 406

<div>
IF OBJECT_ID('ChangeAllObjOwner') IS NOT NULL DROP PROC ChangeAllObjOwner
GO
--定义
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE (
@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

--调用
exec ChangeAllObjOwner 'huiyuan','dbo'
</div>

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　数据库所有者　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)