The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

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

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]