常州网络公司首页 >>> 数据库 >> MSSQL

SQL存储过程的所有者修改方法

发布时间:2010-4-23 0:09:09   浏览次数:7243

在你所需要修改存储过程的数据库里添加如下存储过程,可以直接在查询分析器里运行

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[changename]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[changename]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

Create PROCEDURE dbo.changename
@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

FETCH NEXT FROM curObject INTO @Name, @Owner
END

close curObject
deallocate curObject
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

运行后,再调用此存储过程来修改
exec Changename 'olddbo','dbo' --将olddbo所有者更改为dbo

上一条:sql 2005 端口修改方式
下一条:SQL2005数据库还原之----附加数据库
首页 | 在线留言 | 网站地图

CopyRight ©2008-2014   糊涂蛋技术网    苏ICP备05013073号
站长:万创   QQ:54960248  E-mail:xlxcn#126.com