AlwaysOn, one of the most used MSSQL high availability solutions, will not be created on the other side when a user is created on one server.
You can automatically create the user created in the primary on the other side with the help of the following command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [ddl_login_pswd] ON ALL SERVER FOR ALTER_LOGIN AS DECLARE @secondary_script NVARCHAR(MAX) DECLARE @text NVARCHAR(MAX)= (EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')) if(@text like '%PASSWORD%') BEGIN DECLARE @LoginName NVARCHAR(1000)=(EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')) DECLARE @Password NVARCHAR(512)=(select sys.fn_varbintohexstr((SELECT password_hash FROM sys.sql_logins WHERE name=@LoginName))) SET @secondary_script=CONCAT('EXEC (''ALTER LOGIN ', @LoginName , ' WITH PASSWORD=', @Password , ' HASHED, CHECK_POLICY=OFF'') AT [secondary_sunucu] ') EXEC sp_Executesql @secondary_script SET @secondary_script=CONCAT('EXEC (''ALTER LOGIN ', @LoginName ,' WITH CHECK_POLICY=ON'') AT [secondary_sunucu] ') EXEC sp_Executesql @secondary_script END GO DISABLE TRIGGER [ddl_login_pswd] ON ALL SERVER GO |
