今天恢復(fù)一個(gè)在別人機(jī)器備份的數(shù)據(jù)庫(kù)到SQL SERVER，恢復(fù)后該數(shù)據(jù)庫(kù)中有一個(gè)mooip用戶，該用戶是數(shù)據(jù)庫(kù)所有者，很多表的所有者也是他，但安全性里的登錄中沒(méi)有該用戶，刪除不了該用戶，提示“因?yàn)檫x定的用戶擁有對(duì)象，所以無(wú)法除去該用戶”！ 具體解決方式如下： 1 如果是一張表有此問(wèn)題，解決方式如下： exec sp_changeobjectowner 'mooip.tablename', 'dbo' 這樣就把tablename指定的表的所有者賦值給了dbo用戶

2 如果所有表都存在此問(wèn)題，這是就要編寫存儲(chǔ)過(guò)程實(shí)現(xiàn)批量修改了 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'

這樣就完成了批量修改權(quán)限的工作