declare @ObjectName varchar(256)
-- we are only interested in USER Objects
-- not already owned by 'sa'
-- we don't want keys and constrainst
set @ObjectName = (
select top 1 [name] from sysobjects
where uid <> SUSER_SID('sa')
and [type] in ('FN','IF','P','TF','U','V')
)
declare @ObjectOwner varchar(256)
declare @ObjectFullName varchar(512)
declare @NewOwner varchar(256)
set @NewOwner = 'dbo'
-- default to 'dbo' if null
set @NewOwner = isnull(@NewOwner, 'dbo')
while @ObjectName is not null
begin
select @ObjectOwner = USER_NAME(uid)
from sysobjects where [name] = @ObjectName
set @ObjectFullName = @ObjectOwner + '.' + @Objectname
PRINT 'Changing ownership of ''' + @Objectname +
''' from ''' + @ObjectOwner + ''' to ''' +
@NewOwner + ''''
execute sp_changeobjectowner @ObjectFullName, @NewOwner
set @ObjectName = (select top 1 [name] from sysobjects
where uid <> SUSER_SID('sa')
and [type] in ('FN','IF','P','TF','U','V'))
end
Label List
- Architecture
- Art of SQL Server
- asynchronous mirroring
- csv
- dmv
- find outdated Statistics
- Free SQL Server tools
- index fragmentation
- Installation
- Katmai
- Learn SQL Server 2005
- Maintenance Plan
- outdated Statistics
- Reset Identity column
- Scripts
- SPID
- sql server 2008
- sys.dm_db_index_physical_stats
- sys.dm_exec_connections
- tsql
- update statistics