要快速把資料庫掛上另一台SQL Server,我最常用的作法是複製mdf檔案過去,再直接Attach上去。不過,後續在新SQL上建立與原SQL相同帳號並設定權限的過程,常會遇到以下錯誤訊息:

Microsoft SQL-DMO (ODBC SQLState: 42000) 錯誤 15023: 使用者或角色 '%s' 在目前的資料庫中已經存在。
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.

問題出在複製mdf時,同名使用者(MS KB定義的名詞叫"孤兒使用者")也一併被帶過來,MS KB建議的做法是用sp_change_users_login重新建立新帳號與孤兒使用者間的對應。(註: sp_change_users_login已被宣告未來要廢除,可改用ALTER USER)

不過,我個人比較習慣的做法,是將這些孤兒使用者先一律清除,因為並非所有使用者在新SQL都會有對應的登入帳號,砍掉重練較省事。

以下的T-SQL可以自動列舉出有哪些孤兒使用者,執行清理門戶的任務,供有需要的朋友參考:

【強烈警告】
本程式若使用不當,可能誤殺忠良,導致資料庫問題,請各位自行斟酌風險決定是否使用,行刑前請務必再三確認!

declare @name varchar(32)
declare @stmt nvarchar(1024)
 
declare cur cursor for
--以sys的建立日為基準,之後才建立者視為非系統內建(SQL 2005)
select name from sys.database_principals
where create_date > 
(select create_date from sys.database_principals
 where name = 'sys')
and type in ('S', 'U')
 
open cur
fetch next from cur into @name
 
while @@fetch_status = 0
begin
    set @stmt = 'DROP USER [' + @name + ']'
    print @stmt
    --**警告**建議先印出待刪清單,人工檢核過一次再執行
    --EXEC dbo.sp_executesql @stmt
    fetch next from cur into @name    
end
 
close cur
deallocate cur

Comments

# by smi

http://support.microsoft.com/kb/918992/ 用匯出帳號的方式產生 create login 的script 只要是同一個網域的sql server 掛上mdf的時候不會有孤兒的問題 因為login 的SID 都是相同的

Post a comment