sql server中批量更改对象的所有者

SQL Server存储过程：更改对象所有者

最新推荐文章于 2021-01-19 02:22:09 发布

原创最新推荐文章于 2021-01-19 02:22:09 发布 · 935 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #user

随便写写专栏收录该内容

3 篇文章

订阅专栏

博客展示了一个SQL Server的存储过程User_ChangeObjectOwnerBatch，用于批量更改对象所有者。该过程通过游标遍历sysobjects表中指定旧所有者的对象，然后使用sp_changeobjectowner系统存储过程将对象的所有者更改为新所有者。

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS

DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)

DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name

OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner
END

close curObject
deallocate curObject