# 如何用 T-SQL 一次改變全資料庫中所有物件的 Owner

**SQL 2005**

```sql
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
```

**SQL 2000**

```sql
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
```