При включении базы MSSQL в Always ON необходимо перенести всех пользователей (логины), созданных для этой БД, иначе, если реплика станет мастером, к базе данных будет невозможно подключиться.

Пользователи в двух БД должны иметь ОДИНАКОВЫЙ СИД , т.е. просто создать аналогичного пользователя с паролем НЕДОСТАТОЧНО.

Чтобы создать пользователя на реплике с таким же СИДом, как и на мастере, необходимо:

  1. Создать хранимую процедуру в БД (взято здесь):
  1USE [master]
  2  GO
  3  IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  4  DROP PROCEDURE sp_hexadecimal
  5  GO
  6  CREATE PROCEDURE [dbo].[sp_hexadecimal]
  7  (
  8      @binvalue varbinary(256),
  9      @hexvalue varchar (514) OUTPUT
 10  )
 11  AS
 12  BEGIN
 13      DECLARE @charvalue varchar (514)
 14      DECLARE @i int
 15      DECLARE @length int
 16      DECLARE @hexstring char(16)
 17      SELECT @charvalue = '0x'
 18      SELECT @i = 1
 19      SELECT @length = DATALENGTH (@binvalue)
 20      SELECT @hexstring = '0123456789ABCDEF'
 21
 22      WHILE (@i <= @length)
 23      BEGIN
 24            DECLARE @tempint int
 25            DECLARE @firstint int
 26            DECLARE @secondint int
 27
 28            SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
 29            SELECT @firstint = FLOOR(@tempint/16)
 30            SELECT @secondint = @tempint - (@firstint*16)
 31            SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)
 32
 33            SELECT @i = @i + 1
 34      END 
 35      SELECT @hexvalue = @charvalue
 36  END
 37  go
 38  IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
 39  DROP PROCEDURE sp_help_revlogin
 40  GO
 41  CREATE PROCEDURE [dbo].[sp_help_revlogin]   
 42  (
 43      @login_name sysname = NULL 
 44  )
 45  AS
 46  BEGIN
 47      DECLARE @name                     SYSNAME
 48      DECLARE @type                     VARCHAR (1)
 49      DECLARE @hasaccess                INT
 50      DECLARE @denylogin                INT
 51      DECLARE @is_disabled              INT
 52      DECLARE @PWD_varbinary            VARBINARY (256)
 53      DECLARE @PWD_string               VARCHAR (514)
 54      DECLARE @SID_varbinary            VARBINARY (85)
 55      DECLARE @SID_string               VARCHAR (514)
 56      DECLARE @tmpstr                   VARCHAR (1024)
 57      DECLARE @is_policy_checked        VARCHAR (3)
 58      DECLARE @is_expiration_checked    VARCHAR (3)
 59      Declare @Prefix                   VARCHAR(255)
 60      DECLARE @defaultdb                SYSNAME
 61      DECLARE @defaultlanguage          SYSNAME     
 62      DECLARE @tmpstrRole               VARCHAR (1024)
 63
 64  IF (@login_name IS NULL)
 65  BEGIN
 66      DECLARE login_curs CURSOR 
 67      FOR 
 68          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
 69          FROM  sys.server_principals p 
 70          LEFT JOIN sys.syslogins     l ON ( l.name = p.name ) 
 71          WHERE p.type IN ( 'S', 'G', 'U' ) 
 72            AND p.name <> 'sa'
 73          ORDER BY p.name
 74  END
 75  ELSE
 76          DECLARE login_curs CURSOR 
 77          FOR 
 78              SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name  
 79              FROM  sys.server_principals p 
 80              LEFT JOIN sys.syslogins        l ON ( l.name = p.name ) 
 81              WHERE p.type IN ( 'S', 'G', 'U' ) 
 82                AND p.name = @login_name
 83              ORDER BY p.name
 84
 85          OPEN login_curs 
 86          FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
 87          IF (@@fetch_status = -1)
 88          BEGIN
 89                PRINT 'No login(s) found.'
 90                CLOSE login_curs
 91                DEALLOCATE login_curs
 92                RETURN -1
 93          END
 94
 95          SET @tmpstr = '/* sp_help_revlogin script '
 96          PRINT @tmpstr
 97
 98          SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
 99
100          PRINT @tmpstr
101          PRINT ''
102
103          WHILE (@@fetch_status <> -1)
104          BEGIN
105            IF (@@fetch_status <> -2)
106            BEGIN
107                  PRINT ''
108
109                  SET @tmpstr = '-- Login: ' + @name
110
111                  PRINT @tmpstr
112
113                  SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''')
114                  BEGIN'
115                  Print @tmpstr 
116
117                  IF (@type IN ( 'G', 'U'))
118                  BEGIN -- NT authenticated account/group 
119                    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
120                  END
121                  ELSE 
122                  BEGIN -- SQL Server authentication
123                          -- obtain password and sid
124                          SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
125
126                          EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
127                          EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
128
129                          -- obtain password policy state
130                          SELECT @is_policy_checked     = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
131                          FROM sys.sql_logins 
132                          WHERE name = @name
133
134                          SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END 
135                          FROM sys.sql_logins 
136                          WHERE name = @name
137
138                          SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' 
139                                          + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']'
140
141                          IF ( @is_policy_checked IS NOT NULL )
142                          BEGIN
143                            SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
144                          END
145
146                          IF ( @is_expiration_checked IS NOT NULL )
147                          BEGIN
148                            SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
149                          END
150          END
151
152          IF (@denylogin = 1)
153          BEGIN -- login is denied access
154              SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
155          END
156          ELSE IF (@hasaccess = 0)
157          BEGIN -- login exists but does not have access
158              SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
159          END
160          IF (@is_disabled = 1)
161          BEGIN -- login is disabled
162              SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
163          END 
164
165          SET @Prefix = '
166          EXEC master.dbo.sp_addsrvrolemember @loginame='''
167
168          SET @tmpstrRole=''
169
170          SELECT @tmpstrRole = @tmpstrRole
171              + CASE WHEN sysadmin        = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin'''        ELSE '' END
172              + CASE WHEN securityadmin   = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin'''   ELSE '' END
173              + CASE WHEN serveradmin     = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin'''     ELSE '' END
174              + CASE WHEN setupadmin      = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin'''      ELSE '' END
175              + CASE WHEN processadmin    = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin'''    ELSE '' END
176              + CASE WHEN diskadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin'''       ELSE '' END
177              + CASE WHEN dbcreator       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator'''       ELSE '' END
178              + CASE WHEN bulkadmin       = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin'''       ELSE '' END
179            FROM (
180                      SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName],
181                              sysadmin,
182                              securityadmin,
183                              serveradmin,
184                              setupadmin,
185                              processadmin,
186                              diskadmin,
187                              dbcreator,
188                              bulkadmin
189                      FROM sys.syslogins
190                      WHERE (       sysadmin<>0
191                              OR    securityadmin<>0
192                              OR    serveradmin<>0
193                              OR    setupadmin <>0
194                              OR    processadmin <>0
195                              OR    diskadmin<>0
196                              OR    dbcreator<>0
197                              OR    bulkadmin<>0
198                          ) 
199                          AND name=@name 
200                ) L 
201
202              PRINT @tmpstr
203              PRINT @tmpstrRole
204              PRINT 'END'
205          END 
206          FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 
207      END
208      CLOSE login_curs
209      DEALLOCATE login_curs
210      RETURN 0
211  END

2. Создать пользователя для БД, если еще не создан ( на Мастере);

3. На мастере выполнить:

1 EXEC sp_help_revlogin 'Login_Name'

В ответ будет выдан скрипт для создания пользователя:

 1/* sp_help_revlogin script 
 2** Generated Feb 10 2023 02:20PM on 0xbbeer-sql */
 3 
 4 
 5-- Login: 0xbbeer-User
 6IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'0xbbeer-User')
 7                  BEGIN
 8CREATE LOGIN [0xbbeer-User] WITH PASSWORD = 0x0200B9272D5FFF07F69353C3D8D7D3F0E6CF3 HASHED, SID = 0xFE909D5812669, DEFAULT_DATABASE = [OT], DEFAULT_LANGUAGE = [us_english], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
 9 
10END
11
12Время выполнения: 2023-01-30T22:57:07.9883562+03:00

4. Копируем его и выполняем на реплике.

5. Проверяем возможность подключения к реплике.