changeowner.sql

自动化网络办公系统全部源码officeanywheresystem

use master 

go 


if exists (select * from sysobjects where name = 'sp_ChangeToDBO') 

drop procedure sp_ChangeToDBO 

go 


create procedure sp_ChangeToDBO as 


set nocount on 

declare @db sysname 

declare @execstring varchar(1000) 


--create a cursor for all non-system databases 


declare dbcursor cursor for 

select name from master..sysdatabases 

where name not in ('tempdb', 'pubs', 'northwind', 'master', 'model', 'msdb') 


open dbcursor 

fetch next from dbcursor into @db 


while (@@fetch_status = 0) 

begin 


-- For each database, create a cursor for all non-dbo owned objects and change their owners to dbo 


select @execstring = 'use ' + @db + ' ' + 

'declare @objname sysname 

declare @execstring1 varchar(1000) 

declare @objowner sysname 

declare objcursor cursor for 

select sysusers.name, sysobjects.name 

from sysobjects join sysusers on sysobjects.uid = sysusers.uid 

where sysobjects.uid <> 1 and sysobjects.type in (''u'', ''v'', ''p'') and sysusers.name <> ''INFORMATION_SCHEMA'' 

order by sysobjects.name 


OPEN objcursor 

fetch next from objcursor into @objowner, @objname 

while (@@fetch_status = 0) 

begin 

select @execstring1 = ''sp_changeobjectowner '''''' + @objowner + ''.'' + @objname + '''''',''''dbo'''''' 

exec (@execstring1) 

select @@error 

fetch next from objcursor into @objowner, @objname 

end 

close objcursor 

deallocate objcursor' 


fetch next from dbcursor into @db 

exec (@execstring) 

end 


close dbcursor 

deallocate dbcursor 

go