sql server 批量修改表和存储过程的所有者。
批量修改表的所有者:
EXEC
sp_MSforeachtable
'
exec sp_changeobjectowner
''
?
''
,
''
dbo
''
'
单个修改表所有者:
exec sp_changeobjectowner '要改的表名','dbo'
批量修改存储过程的存储过程:
代码
CREATE
PROCEDURE
ChangeProcOwner
@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 and xtype = ' p '
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
FETCH NEXT FROM curObject INTO @Name , @Owner
END
close curObject
deallocate curObject
GO
@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 and xtype = ' p '
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
FETCH NEXT FROM curObject INTO @Name , @Owner
END
close curObject
deallocate curObject
GO
执行
exec
ChangeProcOwner
'
xx
'
,
'
dbo
'
或者
exec
ChangeProcOwner
'
?
'
,
'
dbo
'