SQL Server 還原後的使用者和登錄映射

我需要將一個數據庫從一個 SQL Server 實例恢復到另一個，我很清楚數據庫使用者將被轉移，但登錄不會。我創建了一個腳本，以便能夠在目標 SQL Server 實例上創建登錄名，包括雜湊密碼和登錄 SID。但是，我不確定登錄​​是在數據庫還原之前還是在數據庫還原之後在目標實例上創建是否會有所不同。

如果先創建登錄然後恢復數據庫，由於SID，數據庫使用者將與現有的相應登錄相應地映射，但是，如果我進行數據庫恢復並在之後創建登錄，他們仍然會自動映射還是會一個需要做一個 ALTER USER WITH LOGIN 或類似的？

我完全同意 Tony Hinkle 的觀點，即在恢復之前或之後創建登錄名並不重要。如果您在恢復後創建登錄，則在此期間，這些使用者將成為孤立使用者，因為他們在伺服器中沒有相應的登錄 (SID)。您還應該注意系統創建的使用者（NT 使用者）及其 SID。

您可以參考以下腳本，以便將登錄名從一台伺服器輕鬆傳輸到另一台伺服器：

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
 DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
   @binvalue varbinary(256),
   @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
 DECLARE @tempint int
 DECLARE @firstint int
 DECLARE @secondint int
 SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
 SELECT @firstint = FLOOR(@tempint/16)
 SELECT @secondint = @tempint - (@firstint*16)
 SELECT @charvalue = @charvalue +
   SUBSTRING(@hexstring, @firstint+1, 1) +
   SUBSTRING(@hexstring, @secondint+1, 1)
 SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
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
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 varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
 DECLARE login_curs CURSOR FOR

     SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
     ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
 DECLARE login_curs CURSOR FOR


     SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
     ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
 PRINT 'No login(s) found.'
 CLOSE login_curs
 DEALLOCATE login_curs
 RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
   PRINT ''
   SET @tmpstr = '-- Login: ' + @name
   PRINT @tmpstr
   IF (@type IN ( 'G', 'U'))
   BEGIN -- NT authenticated account/group

     SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
   END
   ELSE BEGIN -- SQL Server authentication
       -- obtain password and sid
           SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
       EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
       EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

       -- obtain password policy state
       SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
       SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

           SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

       IF ( @is_policy_checked IS NOT NULL )
       BEGIN
         SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
       END
       IF ( @is_expiration_checked IS NOT NULL )
       BEGIN
         SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
       END
   END
   IF (@denylogin = 1)
   BEGIN -- login is denied access
     SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
   END
   ELSE IF (@hasaccess = 0)
   BEGIN -- login exists but does not have access
     SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
   END
   IF (@is_disabled = 1)
   BEGIN -- login is disabled
     SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
   END
   PRINT @tmpstr
 END

 FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

請注意，您需要交叉檢查這些登錄名的權限。

創建登錄名的時間無關緊要。只要 SID 匹配，數據庫使用者就會與登錄相關聯。

每次使用者嘗試連接到數據庫時，SQL Server 都會檢查映射登錄（除非它是包含數據庫）。它通過檢查是否存在具有與使用者的 SID 匹配的 SID 的登錄來執行此操作。因此，登錄的創建時間無關緊要，因為它將在使用者嘗試連接到數據庫時匹配。

引用自：https://dba.stackexchange.com/questions/233566