2011年12月26日 星期一

SQL Server - 搬移資料庫時,如何自動比對帳號SID

        當您要進行資料庫的搬移時,方法有很多種,最簡單的方法,如果您的資料庫版本完全相同時,您可以將相關的資料庫,如 MSDB、Master等資料庫,透過 detach database之後,再將database attach 到新的系統上即可,但是如果是進行升級或是資料庫的版本不相同時,就需要手動方式進行轉移, 搬移的工作大致上,都可以透過手動產生 Script 之後,再貼到新的主機上執行即可,但是搬移後,會有SID不相同的問題需要手動進行修正,在SQL Server 2000的版本上,有 sp_SidMap 可以使用,但很可惜的,在 SQL Server 2005以後就沒有了,所以我手動的寫了下列的語法進行 Database User與 Login User的比對,希望對大家在移轉資料庫上有所幫助。

列出所有Database User:
SELECT name, sid from sysusers
列出所有Login User:
SELECT sid, name FROM sys.syslogins
下列的語法會比對 Database User 與 Login User,當 Login User不存在時,會直接新增,如果存在,則會透過 sys.sp_change_users_login update_one 的語法直接進行 SID 的對應。
use test_db;
declare @sysusers_name varchar(200);
declare @sysusers_sid varbinary(85);
declare @ret_val int;
DECLARE check_user_list CURSOR
FOR
SELECT name, sid from sysusers
WHERE issqluser = 1
    AND   issqlrole = 0
    AND   isapprole = 0
    AND   uid > 4
ORDER BY name
OPEN check_user_list
FETCH NEXT FROM check_user_list INTO
@sysusers_name, @sysusers_sid
WHILE(@@FETCH_STATUS = 0)
 BEGIN
   IF NOT EXISTS (SELECT name FROM sys.syslogins WHERE name = @sysusers_name)
   BEGIN
  EXEC @ret_val = sp_addlogin @loginame = @sysusers_name, @sid = @sysusers_sid
  IF @ret_val <> 0
  BEGIN
    RAISERROR(15497,16,1,@sysusers_name)
    PRINT'MSG ****: Please add the Login : ' + @sysusers_name + ' using sp_addlogin.'
    CONTINUE
  END
   END
   EXEC sys.sp_change_users_login update_one, @sysusers_name, @sysusers_name
   FETCH NEXT FROM check_user_list INTO
   @sysusers_name, @sysusers_sid
 END
--關閉cursor
CLOSE check_user_list
DEALLOCATE check_user_list


 參考連結:
  1. How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
    http://support.microsoft.com/kb/224071/en-us   
  2. 如何在 SQL Server 的執行個體之間傳送登入和密碼
    http://support.microsoft.com/kb/246133
  3. How to resolve permission issues when you move a database between servers that are running SQL Server
    http://support.microsoft.com/kb/240872/en-us

關鍵字:SQL Serversp_SidMapDatabase UserLogin Usersys.sp_change_users_login update_onesys.syslogins

沒有留言:

張貼留言