如何修改数据库表或存储过程的所有者

发布网友发布时间：2022-04-07 17:12

共2个回答

懂视网时间：2022-04-07 21:34

<span style="font-size:18px;">EXEC sp_MSforeachtable 'exec sp_changeobjectowner ''?'',''dbo'' '</span>

2.存储过程全部换成dbo

<span style="font-size:18px;">CREATE PROCEDURE dbo.ChangeObjectOwner 

@OldOwner as NVARCHAR(128)='dbo',--参数原所有者 

@NewOwner as NVARCHAR(128)='dbo'--参数新所有者 

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 
</span>

然后在运行

<span style="font-size:18px;">exec ChangeObjectOwner '数据库','dbo' 
</span>

关于SQL数据表存储过程表名前缀换成dbo代码

标签：存储数据 sql 数据库 dbo

热心网友时间：2022-04-07 18:42

更改当前数据库的所有者。
--批量修改数据库对象的所有者
新建一个存储过程：changename
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[changename]') and OBJECTPROPERTY(id, N'IsProcere') = 1)
drop procere [dbo].[changename]GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOCreate PROCEDURE dbo.changename
@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 name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)BEGINif @Owner=@OldOwnerbeginset @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwnerendFETCH NEXT FROM curObject INTO @Name, @OwnerENDclose curObject
deallocate curObjectGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO以SA登陆查询分析器，选中你要的数据库
执行存储过程
执行exec Changename '原所有者','dbo'
或exec Changename 'dbo,'数据库所有者'
修改MS SQL表用户属性的命令
可以用exec sp_changeobjectowner 'dataname.数据表','dbo'　把表或存储过程中的所有者都改成dbo<--正文内容结束--