select 'deny all on '+name+' to dbo' from sysobjects where xtype='p'select 'grant all on '+name+' to dbo' from sysobjects where xtype='p'把上面的查询结果分别拿来执行即可.
CREATE PROCEDURE dbo.ChangeObjectOwner @OldOwner as NVARCHAR(128),--参数原所有者 @NewOwner as NVARCHAR(128)--参数新所有者 ASDECLARE @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 nameOPEN 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 ENDclose curObject deallocate curObject GO
set nocount on declare @sql varchar(8000) set @sql='' select @Sql=@sql+'exec sp_changeobjectonwer '''+name+''',''dbo'' go ' from sysobjects where xtype='P' exec(@sql)
exec ChangeObjectOwner 'USER','dbo' 不过所有对象都修改了。
--改过程的所有者:cREATE PROCEDURE dbo.ChangeObjectOwner @OldOwner as NVARCHAR(128),--参数原所有者 @NewOwner as NVARCHAR(128)--参数新所有者 ASDECLARE @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 nameOPEN 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 ENDclose curObject deallocate curObject GO 以SA登陆查询分析器 ,选中你要的数据库 执行exec ChangeObjectOwner '原所有者','dbo'
--以下就可以只修改你全部的存储过程了 CREATE PROCEDURE ChangeProcOwner @OldOwner as NVARCHAR(128),--参数原所有者 @NewOwner as NVARCHAR(128)--参数新所有者 ASDECLARE @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 nameOPEN 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 ENDclose curObject deallocate curObject GO--exec ChangeProcOwner 'User','dbo'
@OldOwner as NVARCHAR(128),--参数原所有者
@NewOwner as NVARCHAR(128)--参数新所有者
ASDECLARE @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 nameOPEN 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
ENDclose curObject
deallocate curObject
GO
declare @sql varchar(8000)
set @sql=''
select @Sql=@sql+'exec sp_changeobjectonwer '''+name+''',''dbo'' go ' from sysobjects where xtype='P'
exec(@sql)
不过所有对象都修改了。
@OldOwner as NVARCHAR(128),--参数原所有者
@NewOwner as NVARCHAR(128)--参数新所有者
ASDECLARE @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 nameOPEN 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
ENDclose curObject
deallocate curObject
GO
以SA登陆查询分析器 ,选中你要的数据库
执行exec ChangeObjectOwner '原所有者','dbo'
CREATE PROCEDURE ChangeProcOwner
@OldOwner as NVARCHAR(128),--参数原所有者
@NewOwner as NVARCHAR(128)--参数新所有者
ASDECLARE @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 nameOPEN 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
ENDclose curObject
deallocate curObject
GO--exec ChangeProcOwner 'User','dbo'