مهاجرت لاگین SQL Server بدون Orphaned Users با sp_help_revlogin

مهاجرت امن و بی‌دردسر لاگین‌های SQL Server به سرور جدید با sp_help_revlogin

مهاجرت لاگین‌ها در SQL Server بین سرورها می‌تواند یک چالش پیچیده باشد، به خصوص زمانی که نیاز به حفظ SID (شناسه امنیتی) و جلوگیری از ایجاد Orphaned Users باشد. این راهنما به شما کمک می‌کند تا این فرآیند را به صورت حرفه‌ای و با استفاده از اسکریپت‌های کارآمد (sp_help_revlogin) انجام دهید، تا امنیت و یکپارچگی دسترسی‌ها حفظ شود.

درک چالش: لاگین‌ها و Orphaned Users

در SQL Server، لاگین‌ها (Logins) به سطح سرور مربوط می‌شوند و مسئول احراز هویت کاربران هستند، در حالی که کاربران (Users) به سطح پایگاه داده مربوط می‌شوند و مسئول مجوزهای دسترسی به اشیاء داخل پایگاه داده هستند. ارتباط بین لاگین و کاربر از طریق SID برقرار می‌شود. اگر لاگین با یک SID خاص در سرور مبدأ وجود داشته باشد و سپس یک کاربر با همان SID در یک پایگاه داده ایجاد شود، این دو به هم متصل می‌شوند.
مشکل زمانی پیش می‌آید که یک پایگاه داده به سرور جدیدی منتقل می‌شود، اما لاگین مربوطه به درستی منتقل نمی‌شود یا با SID متفاوتی ایجاد می‌گردد. در این حالت، کاربر پایگاه داده وجود دارد اما هیچ لاگین سروری برای احراز هویت آن وجود ندارد، و به آن “Orphaned User” گفته می‌شود.

راهکار جامع: استفاده از اسکریپت sp_help_revlogin

بهترین راه برای مهاجرت لاگین‌ها در SQL Server، استفاده از اسکریپتی است که لاگین‌ها را همراه با SIDهایشان استخراج کند. مایکروسافت یک رویه ذخیره‌شده مفید به نام `sp_help_revlogin` را ارائه می‌دهد که این کار را به خوبی انجام می‌دهد. این رویه، اسکریپت‌های `CREATE LOGIN` را تولید می‌کند که شامل اطلاعات SID اصلی لاگین‌ها هستند، بنابراین از ایجاد Orphaned Users جلوگیری می‌کند.

ایجاد رویه ذخیره‌شده sp_help_revlogin

برای استفاده از این روش، ابتدا باید رویه ذخیره‌شده `sp_help_revlogin` را در سرور مبدأ ایجاد کنید. این اسکریپت را می‌توانید در پایگاه داده `master` یا هر پایگاه داده سیستم دیگری که برای رویه‌های عمومی استفاده می‌کنید، اجرا کنید.

USE master
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
BEGIN
SET NOCOUNT ON
DECLARE @name             sysname
DECLARE @type             varchar (1)
DECLARE @hasaccess        int
DECLARE @denylogin        int
DECLARE @is_disabled      int
DECLARE @PWD_varbinary    varbinary (256)
DECLARE @PWD_string       varchar (514)
DECLARE @SID              varbinary (85)
DECLARE @SID_string       varchar (514)
DECLARE @SQLstatement     varchar (1024)
DECLARE @temp_SID         varbinary (85)
DECLARE @temp_SQLstatement  varchar(1024)
DECLARE @is_policy_checked  varchar(3)
DECLARE @is_expiration_checked varchar(3)

DECLARE @Logins CURSOR
SET @Logins = CURSOR FOR
  SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, p.is_policy_checked, p.is_expiration_checked
  FROM sys.server_principals p
  WHERE p.type IN ('S', 'G', 'U') AND p.name  'sa' AND p.sid IS NOT NULL AND p.sid  0x01
  ORDER BY p.name

OPEN @Logins

FETCH NEXT FROM @Logins INTO @SID, @name, @type, @is_disabled, @PWD_varbinary, @is_policy_checked, @is_expiration_checked

WHILE (@@fetch_status = 0)
BEGIN
  IF (@type = 'S')
  BEGIN
    SET @temp_SID = @SID
    SELECT @PWD_varbinary = password_hash
    FROM sys.sql_logins
    WHERE sid = @temp_SID

    IF (@PWD_varbinary IS NOT NULL)
    BEGIN
      SET @PWD_string = '0x' + CONVERT(varchar(514), @PWD_varbinary, 2)
      SET @SID_string = '0x' + CONVERT(varchar(514), @SID, 2)
      SET @SQLstatement = 'CREATE LOGIN ' + QUOTENAME(@name) +
                          ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string +
                          ', DEFAULT_DATABASE = ' + QUOTENAME(CASE WHEN @PWD_varbinary IS NULL THEN 'master' ELSE 'master' END) + ';'
      IF ( (SELECT is_policy_checked FROM sys.sql_logins WHERE sid = @temp_SID) = 1 )
      BEGIN
        SET @SQLstatement = @SQLstatement + CHAR(13) + 'ALTER LOGIN ' + QUOTENAME(@name) + ' WITH CHECK_POLICY = ON;'
      END
      ELSE
      BEGIN
        SET @SQLstatement = @SQLstatement + CHAR(13) + 'ALTER LOGIN ' + QUOTENAME(@name) + ' WITH CHECK_POLICY = OFF;'
      END
      IF ( (SELECT is_expiration_checked FROM sys.sql_logins WHERE sid = @temp_SID) = 1 )
      BEGIN
        SET @SQLstatement = @SQLstatement + CHAR(13) + 'ALTER LOGIN ' + QUOTENAME(@name) + ' WITH CHECK_EXPIRATION = ON;'
      END
      ELSE
      BEGIN
        SET @SQLstatement = @SQLstatement + CHAR(13) + 'ALTER LOGIN ' + QUOTENAME(@name) + ' WITH CHECK_EXPIRATION = OFF;'
      END
    END
    ELSE
    BEGIN
      -- Windows Login (Group or User)
      SET @SID_string = '0x' + CONVERT(varchar(514), @SID, 2)
      SET @SQLstatement = 'CREATE LOGIN ' + QUOTENAME(@name) +
                          ' FROM WINDOWS WITH SID = ' + @SID_string +
                          ', DEFAULT_DATABASE = ' + QUOTENAME(CASE WHEN @PWD_varbinary IS NULL THEN 'master' ELSE 'master' END) + ';'
    END
  END
  ELSE
  BEGIN
    -- Windows Login (Group or User)
    SET @SID_string = '0x' + CONVERT(varchar(514), @SID, 2)
    SET @SQLstatement = 'CREATE LOGIN ' + QUOTENAME(@name) +
                        ' FROM WINDOWS WITH SID = ' + @SID_string +
                        ', DEFAULT_DATABASE = ' + QUOTENAME(CASE WHEN @PWD_varbinary IS NULL THEN 'master' ELSE 'master' END) + ';'
  END

  IF (@is_disabled = 1)
  BEGIN
    SET @SQLstatement = @SQLstatement + CHAR(13) + 'ALTER LOGIN ' + QUOTENAME(@name) + ' DISABLE;'
  END
  
  PRINT @SQLstatement
  PRINT 'GO'

  FETCH NEXT FROM @Logins INTO @SID, @name, @type, @is_disabled, @PWD_varbinary, @is_policy_checked, @is_expiration_checked
END
CLOSE @Logins
DEALLOCATE @Logins
END
GO

این رویه ذخیره‌شده، از کاتالوگ‌های سیستمی SQL Server برای بازیابی اطلاعات لاگین‌ها، شامل SID و هش رمز عبور (برای لاگین‌های SQL Server) استفاده می‌کند. سپس دستورات `CREATE LOGIN` مناسب را تولید می‌کند.

اجرا و مهاجرت لاگین‌ها

پس از ایجاد `sp_help_revlogin` در سرور مبدأ، آن را اجرا کنید تا اسکریپت‌های `CREATE LOGIN` برای تمام لاگین‌های موجود تولید شود:

EXEC sp_help_revlogin

خروجی این دستور، مجموعه‌ای از دستورات T-SQL خواهد بود. این خروجی را کپی کرده و در یک فایل متنی ذخیره کنید. سپس، این فایل اسکریپت را در سرور مقصد اجرا کنید. این کار تمامی لاگین‌ها را با SIDهای اصلی‌شان در سرور جدید ایجاد می‌کند.

رسیدگی به Orphaned Users

پس از مهاجرت لاگین‌ها و پیوست کردن پایگاه‌های داده به سرور جدید، ممکن است هنوز کاربرانی وجود داشته باشند که SID آن‌ها با هیچ لاگینی مطابقت ندارد. این اتفاق معمولاً به دلیل انتقال دستی پایگاه داده یا عدم موفقیت در انتقال برخی لاگین‌ها رخ می‌دهد.
برای شناسایی Orphaned Users در یک پایگاه داده خاص، دستور زیر را اجرا کنید:

USE [YourDatabaseName] -- نام پایگاه داده خود را جایگزین کنید
GO
EXEC sp_change_users_login 'Report'

این دستور لیستی از کاربران پایگاه داده را نمایش می‌دهد که لاگین سروری متناظر با SID خود ندارند.
برای رفع مشکل Orphaned Users، دو راهکار وجود دارد:

1. **مپ کردن کاربر به لاگین موجود:** اگر لاگین مربوطه قبلاً با SID صحیح در سرور مقصد ایجاد شده است، می‌توانید کاربر را به آن لاگین متصل کنید:

    USE [YourDatabaseName]
    GO
    ALTER USER [UserName] WITH LOGIN = [LoginName]
    

در این دستور، `[UserName]` نام Orphaned User و `[LoginName]` نام لاگین سروری است که می‌خواهید کاربر را به آن متصل کنید.
2. **ایجاد لاگین جدید برای Orphaned User:** اگر لاگین مربوطه وجود ندارد و نیاز به ایجاد آن است، می‌توانید از دستور `sp_change_users_login ‘Auto_Fix’` استفاده کنید (اگر لاگین سروری با همان نام کاربر وجود نداشته باشد). اما روش توصیه شده این است که لاگین را با SID صحیح ایجاد کرده و سپس آن را مپ کنید.

بهترین شیوه‌ها و نکات مهم

* **تست در محیط غیرتولیدی:** همیشه قبل از اجرای هرگونه تغییر در محیط تولید، فرآیند مهاجرت را در یک محیط تست یا توسعه شبیه‌سازی و بررسی کنید.
* **بکاپ‌گیری:** قبل از شروع فرآیند، از تمامی پایگاه‌های داده و لاگین‌های سرور مبدأ و مقصد، بکاپ کامل تهیه کنید.
* **بررسی مجوزها:** اطمینان حاصل کنید که لاگین `sa` یا لاگینی که با آن کار می‌کنید، دارای مجوزهای کافی (مانند `ALTER ANY LOGIN` و `CONTROL SERVER`) برای ایجاد و تغییر لاگین‌ها و کاربران باشد.
* **لاگین‌های سیستمی:** لاگین‌هایی مانند `##MS_SQLResourceGroup##` و `##MS_AgentSigningCertificate##` که توسط SQL Server ایجاد می‌شوند، نیازی به مهاجرت ندارند. `sp_help_revlogin` به طور خودکار این موارد را فیلتر می‌کند.

نتیجه‌گیری

مهاجرت لاگین‌ها در SQL Server با استفاده از رویه ذخیره‌شده `sp_help_revlogin` یک روش کارآمد و امن برای حفظ SIDها و جلوگیری از ایجاد Orphaned Users است. با دنبال کردن این راهنما و رعایت بهترین شیوه‌ها، می‌توانید این فرآیند حیاتی را با اطمینان و دقت بالا انجام دهید و از بروز مشکلات دسترسی در محیط جدید جلوگیری کنید. این رویکرد، یک گام مهم در حفظ امنیت و کارایی سیستم‌های SQL Server شما در زمان انتقال یا ارتقاء سرورهاست.