CODE-批次清除SQL資料庫的孤兒使用者

要快速把資料庫掛上另一台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
歡迎推文分享:
Published 17 March 2010 06:45 PM 由 Jeffrey
Filed under:
Views: 9,878



意見

# smi said on 17 March, 2010 09:07 PM

support.microsoft.com/.../918992

用匯出帳號的方式產生 create login 的script

只要是同一個網域的sql server

掛上mdf的時候不會有孤兒的問題

因為login 的SID 都是相同的

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<March 2010>
SunMonTueWedThuFriSat
28123456
78910111213
14151617181920
21222324252627
28293031123
45678910
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication