При включении базы MSSQL в Always ON необходимо перенести всех пользователей (логины), созданных для этой БД, иначе, если реплика станет мастером, к базе данных будет невозможно подключиться.
Пользователи в двух БД должны иметь ОДИНАКОВЫЙ СИД , т.е. просто создать аналогичного пользователя с паролем НЕДОСТАТОЧНО.
Чтобы создать пользователя на реплике с таким же СИДом, как и на мастере, необходимо:
- Создать хранимую процедуру в БД (взято здесь):
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. Проверяем возможность подключения к реплике.