批量修改数据库或存储过程的所有者

转载于 2014-04-24 10:45:00 发布 · 263 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Chaser-Eagle/p/3684892.html

文章标签：

#数据库

本文介绍了一种在SQL Server中批量更改表所有者的存储过程。通过创建User_ChangeObjectOwnerBatch存储过程，可以将指定旧所有者的表的所有权转移给新所有者，适用于大量表的权限调整场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

更改某个表的用户（[用户名].表名)

exec sp_changeobjectowner 'tablename','dbo'

--存储更改全部表

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


GO