SQL 2005
[code:tsql]
DECLARE @obj sysname
DECLARE @oldowner varchar(100)
DECLARE @newowner varchar(100)
DECLARE @sql varchar(1000)
SET @oldowner = 'YOUR_OLD_OWNER_NAME'
SET @newowner = 'dbo'
-- U Table
-- TF Function
-- P Stored Procedure
DECLARE tblcur INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects
WHERE (xtype = 'U' or xtype = 'P' or xtype = 'TF') AND uid = user_id(@oldowner)
OPEN tblcur
WHILE 1 = 1
BEGIN
FETCH tblcur INTO @obj
IF @@fetch_status <0 BREAK
SET @sql = 'exec sp_changeobjectowner ''' + @oldowner + '.' + @obj + ''', ''' + @newowner +''''
exec(@sql)
END
DEALLOCATE tblcur
[/code]
SQL 2000
[code:tsql]
DECLARE @obj sysname
DECLARE @oldowner varchar(100)
DECLARE @newowner varchar(100)
SET @oldowner = 'YOUR_OLD_OWNER_NAME'
SET @newowner = 'dbo'
-- U Table
-- TF Function
-- P Stored Procedure
DECLARE tblcur INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects
WHERE (xtype = 'U' or xtype = 'P' or xtype = 'TF') AND uid = user_id(@oldowner)
OPEN tblcur
WHILE 1 = 1
BEGIN
FETCH tblcur INTO @obj
IF @@fetch_status <0 BREAK
EXEC sp_changeobjectowner @obj, @oldowner, @newowner
END
DEALLOCATE tblcur
[/code]