sp_changeobjectowner 更改当前数据库中对象的所有者。语法 sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'参数 [@objname =] 'object'当前数据库中现有的表、视图或存储过程的名称。object 的数据类型为 nvarchar(517),没有默认值。object 可用现有对象所有者限定,格式为 existing_owner.object。[@newowner =] 'owner'即将成为对象的新所有者的安全帐户的名称。owner 的数据类型为 sysname,没有默认值。owner 必须是当前数据库中有效的 Microsoft® SQL Server™ 用户或角色或 Microsoft Windows NT® 用户或组。指定 Windows NT 用户或组时,请指定 Windows NT 用户或组在数据库中已知的名称(用 sp_grantdbaccess 添加)。 --把你的那个表前面加上它的所有者。否则是不能识别的。 sp_changeobjectowner '非dbo对象.yourtabel' , 'dbo'
建立自己的sp_MSforeachObject:USE MASTER GOCREATE proc sp_MSforeachObject @objectType int=1, --对象类型,可以是下列对象类型中的一种或组合: --C = CHECK 约束 --D = 默认值或 DEFAULT 约束 --F = FOREIGN KEY 约束 --L = 日志 --FN = 标量函数 --IF = 内嵌表函数 --P = 存储过程 --PK = PRIMARY KEY 约束(类型是 K) --RF = 复制筛选存储过程 --S = 系统表 --TF = 表函数 --TR = 触发器 --U = 用户表 --UQ = UNIQUE 约束(类型是 K) --V = 视图 --X = 扩展存储过程 @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null, @precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null as declare @mscat nvarchar(12) select @mscat = ltrim(str(convert(int, 0x0002)))if (@precommand is not null) exec(@precommand)/* Defined @isobject for save object type */ Declare @isobject varchar(256)select @isobject= case @objectType when 1 then 'IsUserTable' when 2 then 'IsView' when 3 then 'IsTrigger' when 4 then 'IsProcedure' when 5 then 'IsDefault' when 6 then 'IsForeignKey' when 7 then 'IsScalarFunction' when 8 then 'IsInlineFunction' when 9 then 'IsPrimaryKey' when 10 then 'IsExtendedProc' when 11 then 'IsReplProc' when 12 then 'IsRule' end/* Create the select */ /* Use @isobject variable isstead of IsUserTable string */ EXEC(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o ' + N' where OBJECTPROPERTY(o.id, N'''+@isobject+''') = 1 '+N' and o.category & ' + @mscat + N' = 0 ' + @whereand)declare @retval int select @retval = @@error if (@retval = 0) exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3if (@retval = 0 and @postcommand is not null) exec(@postcommand)return @retval GO这样我们来测试一下: 1.获得所有的存储过程的脚本: EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=4 2.获得所有的视图的脚本: EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=23.比如在开发过程中,没一个用户都是自己的OBJECT OWNER,所以在真实的数据库时都要改为DBO: EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=1 EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=2 EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=3 EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=4 这样就非常方便的将每一个数据库对象改为DBO.当然还要很多非常好的功能,大家可以自己深入研究吧:-)
declare tb cursor local for select 'sp_changeobjectowner ''['+replace(user_name(uid),']',']]')+'].[' +replace(name,']',']]')+']'',''dbo''' from sysobjects where xtype in('U','V','P','TR','FN','IF','TF') and status>=0 open tb declare @s nvarchar(4000) fetch tb into @s while @@fetch_status=0 begin exec(@s) fetch tb into @s end close tb deallocate tb go
游标法:DECLARE @CursorVar CURSOR declare @tablename varchar(40) declare @tb1 varchar(40) declare @dlycnt int --IsProcedure DECLARE Bti_cursor CURSOR FOR select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsTable') = 1 and uid <> 1 --(默认情况下1就是指sa用户) set @CursorVar = Bti_cursor open @CursorVar set @dlycnt = 0 FETCH NEXT FROM @CursorVar into @tablename WHILE @@FETCH_STATUS = 0 BEGIN select @tb1 = 'ccuser.'+ @tablename --修改USER为你的帐户,必须以这个帐号登陆,因为SA已经不能访问了。 EXEC sp_changeobjectowner @tb1,'dbo' select @dlycnt = @dlycnt + 1 FETCH NEXT FROM @CursorVar into @tablename END CLOSE Bti_cursor SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO 使用存储过程 exec dbo.sp_msforeachtable @command1=N'exec dbo.sp_changeobjectowner N''*'',N''新所有者''', @replacechar=N'*', @whereand=N' and uid=1', @precommand=N' print ''处理之前执行的SQL语句''', @postcommand=N' print ''处理之后执行的SQL语句'''
更改当前数据库中对象的所有者。语法
sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'参数
[@objname =] 'object'当前数据库中现有的表、视图或存储过程的名称。object 的数据类型为 nvarchar(517),没有默认值。object 可用现有对象所有者限定,格式为 existing_owner.object。[@newowner =] 'owner'即将成为对象的新所有者的安全帐户的名称。owner 的数据类型为 sysname,没有默认值。owner 必须是当前数据库中有效的 Microsoft® SQL Server™ 用户或角色或 Microsoft Windows NT® 用户或组。指定 Windows NT 用户或组时,请指定 Windows NT 用户或组在数据库中已知的名称(用 sp_grantdbaccess 添加)。
--把你的那个表前面加上它的所有者。否则是不能识别的。
sp_changeobjectowner '非dbo对象.yourtabel' , 'dbo'
GOCREATE proc sp_MSforeachObject
@objectType int=1, --对象类型,可以是下列对象类型中的一种或组合:
--C = CHECK 约束
--D = 默认值或 DEFAULT 约束
--F = FOREIGN KEY 约束
--L = 日志
--FN = 标量函数
--IF = 内嵌表函数
--P = 存储过程
--PK = PRIMARY KEY 约束(类型是 K)
--RF = 复制筛选存储过程
--S = 系统表
--TF = 表函数
--TR = 触发器
--U = 用户表
--UQ = UNIQUE 约束(类型是 K)
--V = 视图
--X = 扩展存储过程
@command1 nvarchar(2000),
@replacechar nchar(1) = N'?',
@command2 nvarchar(2000) = null,
@command3 nvarchar(2000) = null,
@whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null,
@postcommand nvarchar(2000) = null
as
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))if (@precommand is not null)
exec(@precommand)/* Defined @isobject for save object type */
Declare @isobject varchar(256)select @isobject= case @objectType when 1 then 'IsUserTable'
when 2 then 'IsView'
when 3 then 'IsTrigger'
when 4 then 'IsProcedure'
when 5 then 'IsDefault'
when 6 then 'IsForeignKey'
when 7 then 'IsScalarFunction'
when 8 then 'IsInlineFunction'
when 9 then 'IsPrimaryKey'
when 10 then 'IsExtendedProc'
when 11 then 'IsReplProc'
when 12 then 'IsRule'
end/* Create the select */
/* Use @isobject variable isstead of IsUserTable string */
EXEC(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from dbo.sysobjects o '
+ N' where OBJECTPROPERTY(o.id, N'''+@isobject+''') = 1 '+N' and o.category & ' + @mscat + N' = 0 '
+ @whereand)declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3if (@retval = 0 and @postcommand is not null)
exec(@postcommand)return @retval
GO这样我们来测试一下:
1.获得所有的存储过程的脚本:
EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=4
2.获得所有的视图的脚本:
EXEc sp_MSforeachObject @command1="sp_helptext '?' ",@objectType=23.比如在开发过程中,没一个用户都是自己的OBJECT OWNER,所以在真实的数据库时都要改为DBO:
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=1
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=2
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=3
EXEc sp_MSforeachObject @command1="sp_changeobjectowner '?', 'dbo'",@objectType=4 这样就非常方便的将每一个数据库对象改为DBO.当然还要很多非常好的功能,大家可以自己深入研究吧:-)
declare tb cursor local for
select 'sp_changeobjectowner ''['+replace(user_name(uid),']',']]')+'].['
+replace(name,']',']]')+']'',''dbo'''
from sysobjects
where xtype in('U','V','P','TR','FN','IF','TF') and status>=0
open tb
declare @s nvarchar(4000)
fetch tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch tb into @s
end
close tb
deallocate tb
go
declare @tablename varchar(40)
declare @tb1 varchar(40)
declare @dlycnt int --IsProcedure
DECLARE Bti_cursor CURSOR FOR
select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsTable') = 1 and uid <> 1 --(默认情况下1就是指sa用户)
set @CursorVar = Bti_cursor
open @CursorVar
set @dlycnt = 0
FETCH NEXT FROM @CursorVar into @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
select @tb1 = 'ccuser.'+ @tablename --修改USER为你的帐户,必须以这个帐号登陆,因为SA已经不能访问了。
EXEC sp_changeobjectowner @tb1,'dbo'
select @dlycnt = @dlycnt + 1
FETCH NEXT FROM @CursorVar into @tablename
END
CLOSE Bti_cursor
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
使用存储过程
exec dbo.sp_msforeachtable
@command1=N'exec dbo.sp_changeobjectowner N''*'',N''新所有者''',
@replacechar=N'*',
@whereand=N' and uid=1',
@precommand=N' print ''处理之前执行的SQL语句''',
@postcommand=N' print ''处理之后执行的SQL语句'''