WEB开发网
开发学院数据库MSSQL Server 更改数据库中表所属用户的两个方法 阅读

更改数据库中表所属用户的两个方法

 2007-12-27 15:31:22 来源:WEB开发网   
核心提示:更改某个表exec sp_changeobjectowner 'tablename','dbo'存储更改全部表CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch@OldOwneras NVARCHAR(128),@NewOwneras NVA

更改某个表

exec sp_changeobjectowner 'tablename','dbo'

存储更改全部表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwneras NVARCHAR(128),
@NewOwneras 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
--select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO

Tags:更改 数据库 中表

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接