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

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

use dbname

sp_changedbowner 'new_user'

更改当前数据库的所有者。

--批量修改数据库对象的所有者

新建一个存储过程:changename

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

以SA登陆查询分析器 ,选中你要的数据库

执行存储过程

执行exec Changename '原所有者','dbo'

或exec Changename 'dbo,'数据库所有者'

修改MS SQL表用户属性的命令

可以用exec sp_changeobjectowner 'dataname.数据表','dbo'把表或存储过程中的所有者都改成dbo

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航