ms sql批量修改許可權

今天恢複一個在別人機器備份的資料庫到SQL SERVER，恢複後該資料庫中有一個mooip使用者，該使用者是資料庫擁有者，很多表的所有者也是他，但安全性裡的登入中沒有該使用者，刪除不了該使用者，提示“因為選定的使用者擁有對象，所以無法除去該使用者”！

具體解決方式如下：
      1 如果是一張表有此問題，解決方式如下：
       
       exec sp_changeobjectowner 'mooip.tablename', 'dbo'
     
       這樣就把tablename指定的表的所有者賦值給了dbo使用者

     2 如果所有表都存在此問題，這是就要編寫預存程序實現批量修改了
       
         IF OBJECT_ID('ChangeAll') IS NOT NULL Drop PROC ChangeAll
         GO

         Create PROCEDURE ChangeAll (
             @old sysname,
             @new sysname
         )
        AS
             DECLARE @objname sysname

             SET NOCOUNT ON

             IF USER_ID(@old) IS NULL
                BEGIN
                    RAISERROR ('The @old passed does not exist in the database', 16, 1)
                    RETURN
                END
             IF USER_ID(@new) IS NULL
                BEGIN
                    RAISERROR ('The @new passed does not exist in the database', 16, 1)
                    RETURN
                END

             DECLARE owner_cursor CURSOR FOR

             Select name FROM sysobjects Where uid = USER_ID(@old)
             OPEN owner_cursor
             FETCH NEXT FROM owner_cursor INTO @objname
             WHILE (@@fetch_status <> -1)
                 BEGIN
                     SET @objname = @old + '.' + @objname
                     EXEC sp_changeobjectowner @objname, @new
                     FETCH NEXT FROM owner_cursor INTO @objname
                 END

            CLOSE owner_cursor
            DEALLOCATE owner_cursorz
            GO

EXEC ChangeAll @old = 'mooip', @new = 'dbo'

這樣就完成了批量修改許可權的工作