这个不仅能改表的所有者,还能改视图存储过程等对象的所有者,一次性的!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
更改当前数据库的所有者。语法
sp_changedbowner [ @loginame = ] 'login'
[ , [ @map = ] remap_alias_flag ]参数
[@loginame =] 'login'当前数据库新所有者的登录 ID。login 的数据类型为 sysname,没有默认值。login 必须是已存在的 Microsoft® SQL Server™ 登录或 Microsoft Windows NT® 用户。如果 login 通过当前数据库内的现有别名或用户安全帐户已拥有访问该数据库的权限,则不能成为该数据库的所有者。为了避免这种情况,应先除去当前数据库中的别名或用户。[@map =] remap_alias_flag值为 true 或 false,表示旧数据库所有者 (dbo) 的现有别名是映射到当前数据库的新所有者还是要除去。remap_alias_flag 的数据类型为 varchar(5),默认值为 NULL,表示旧 dbo 的任何现有别名均映射到当前数据库的新所有者。false 表示除去旧数据库所有者的现有别名。返回代码值
0(成功)或 1(失败)
@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