SQL Server實例間同步登錄用戶

来源:https://www.cnblogs.com/lyhabc/archive/2023/09/11/17691892.html
-Advertisement-
Play Games

SQL Server實例間同步登錄用戶 問題痛點：由於AlwaysOn和資料庫鏡像無法同步資料庫外實例對象，例如 登錄用戶、作業、鏈接伺服器等，導致主庫切換之後，應用連接不上資料庫或者作業不存在導致每晚跑批任務漏跑等 目前來看，作業等其他實例對象的同步還比較難實現，比如作業分為很多步驟，而且作業包含 ...


SQL Server實例間同步登錄用戶

問題痛點：由於AlwaysOn和資料庫鏡像無法同步資料庫外實例對象，例如 登錄用戶、作業、鏈接伺服器等，導致主庫切換之後，應用連接不上資料庫或者作業不存在導致每晚跑批任務漏跑等

目前來看，作業等其他實例對象的同步還比較難實現，比如作業分為很多步驟，而且作業包含的命令也比較複雜，作業也支持調用其他子系統，比如 PowerShell ，ActiveX，CmdExec等資料庫外部程式和命令，用動態SQL方式很難處理

本文主要介紹的是登錄用戶的同步，畢竟登錄用戶的重要性還是比較高的，應用需要先通過登錄用戶登錄DB實例才能執行後續的操作

 

要在SQLServer實例間同步登錄用戶，主要有幾種方法

1、創建操作系統域用戶，然後創建基於這個域用戶的登錄用戶，因為域用戶在域裡面是同步的，但是這種方法前提是需要有域環境，而且普通開發人員一般也沒有域控機器許可權創建域用戶

2、使用外部第三方工具，比如 sqlcmd，PowerShell

3、使用鏈接伺服器 和 動態拼接SQL方法

 

本文主要使用第三種方法，因為第三種方法本人認為有下麵幾種優勢

1、保證最低維護成本，純SQL實現，不需要藉助第三方工具

2、通用性，幾乎所有SQL Server版本都能用，也不需要像第三方工具例如 PowerShell那樣有時候需要升級版本

3、相容性，跨操作系統平臺Linux、Windows

4、高可靠性，使用SQLServer自帶原生工具，足夠簡單高效

 

 

這個工具腳本的主要流程如下

 

 

具體使用步驟

假設有三個AlwaysOn節點，分別是

node1 ip：192.168.10.10

node2 ip：192.168.10.11

node3 ip：192.168.10.12

step1： 創建鏈接伺服器，在所有AlwaysOn節點上創建其他節點的鏈接伺服器，比如在192.168.10.10上創建其他節點鏈接伺服器，下麵腳本在192.168.10.10伺服器上執行，其他節點以此類推

--create  linkedserver
USE [master]
GO

DECLARE @IP NVARCHAR(MAX)
DECLARE @Login NVARCHAR(MAX)
DECLARE @PWD NVARCHAR(MAX)

SET @Login = N'sa' --★Do
SET @PWD = N'xxxxxx'  --★Do
SET  @IP ='192.168.10.11,1433'    --★Do


EXEC master.dbo.sp_addlinkedserver @server = @IP,@srvproduct = N'SQL Server'

EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation compatible', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'data access', @optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'dist',@optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'pub',@optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc',@optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc out',@optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'sub',@optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'0'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation name', @optvalue = NULL
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'lazy schema validation', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'0'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'use remote collation', @optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'120'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'120'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'remote proc transaction promotion',@optvalue = N'true'

USE [master]
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = @IP,
@locallogin = NULL, 
@useself = N'False', 
@rmtuser = @Login,
@rmtpassword = @PWD



---------------------------------------------------------------------------------------------------------------------------

--create  linkedserver
USE [master]
GO

DECLARE @IP NVARCHAR(MAX)
DECLARE @Login NVARCHAR(MAX)
DECLARE @PWD NVARCHAR(MAX)

SET @Login = N'sa' --★Do
SET @PWD = N'xxxxxx'  --★Do
SET  @IP ='192.168.10.12,1433'    --★Do


EXEC master.dbo.sp_addlinkedserver @server = @IP,@srvproduct = N'SQL Server'

EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation compatible', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'data access', @optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'dist',@optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'pub',@optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc',@optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc out',@optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'sub',@optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'0'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation name', @optvalue = NULL
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'lazy schema validation', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'0'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'use remote collation', @optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'120'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'120'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'remote proc transaction promotion',@optvalue = N'true'

USE [master]
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = @IP,
@locallogin = NULL, 
@useself = N'False', 
@rmtuser = @Login,
@rmtpassword = @PWD

 

step2： 創建存儲過程，在所有AlwaysOn節點上創建存儲過程，記住是所有AlwaysOn節點都要執行

USE [master]
GO
-- =================================================================
-- Author:        <steven>
-- Create date: <2021-12-26>
-- Description:    <Synchronize login users between multiple SQLServer Instances>
-- =================================================================
create  PROCEDURE [dbo].[usp_SyncLoginUserRegularBetweenInstances]
AS
BEGIN

      IF EXISTS(SELECT  1   FROM    sys.dm_hadr_availability_replica_states hars 
              INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id
              INNER JOIN sys.availability_replicas ar ON ar.replica_id = hars.replica_id
      WHERE   [hars].[is_local] = 1 AND [hars].[role_desc] = 'PRIMARY'AND [hars].[operational_state_desc] = 'ONLINE'
              AND [hars].[synchronization_health_desc] = 'HEALTHY')
      BEGIN
               ----Check for prerequisite, if not present deploy it.
               IF NOT EXISTS (SELECT  id  FROM  [master].[dbo].[sysobjects] where name='sp_hexadecimal' and xtype='P')  
               BEGIN
                     DECLARE @sp_hexadecimalcreatescript NVARCHAR(3000)
                     SET @sp_hexadecimalcreatescript =  N'
                  CREATE PROCEDURE [dbo].[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;'
                           
                           EXEC [master].[dbo].sp_executesql @sp_hexadecimalcreatescript
               END
               
                              
               
               DECLARE @TempTable TABLE
               (id INT IDENTITY ,Script NVARCHAR(MAX))
               DECLARE @Login NVARCHAR(MAX)
               DECLARE CURLOGIN CURSOR FOR
               SELECT name 
               FROM sys.server_principals
               WHERE [type] = 'S' AND  [is_disabled] =0   AND  [name] <> 'sa'
               --WHERE CONVERT(VARCHAR(24), create_date, 103) = CONVERT(VARCHAR(24), GETDATE(), 103)
               --    OR CONVERT(VARCHAR(24), modify_date, 103) = CONVERT(VARCHAR(24), GETDATE(), 103)
               
               OPEN CURLOGIN
               FETCH NEXT FROM CURLOGIN INTO @Login
               
               WHILE @@FETCH_STATUS = 0
               BEGIN
                   SET NOCOUNT ON
                   DECLARE @Script NVARCHAR(MAX)
                   DECLARE @LoginName VARCHAR(1500) = @Login
                   DECLARE @LoginSID VARBINARY(400)
                   DECLARE @SID_String VARCHAR(1514)
                   DECLARE @LoginPWD VARBINARY(1256)
                   DECLARE @PWD_String VARCHAR(1514)
                   DECLARE @LoginType CHAR(1)
                   DECLARE @is_disabled BIT
                   DECLARE @default_database_name SYSNAME
                   DECLARE @default_language_name SYSNAME
                   DECLARE @is_policy_checked BIT
                   DECLARE @is_expiration_checked BIT
                   DECLARE @createdDateTime DATETIME
               
                   SELECT @LoginSID = P.[sid]
                       , @LoginType = P.[type]
                       , @is_disabled = P.is_disabled 
                       , @default_database_name = P.default_database_name 
                       , @default_language_name = P.default_language_name 
                       , @createdDateTime = P.create_date 
                   FROM sys.server_principals P
                   WHERE P.name = @LoginName
               
                   SET @Script = ''
               
                   --If the login is a SQL Login, then do a lot of stuff...
                   IF @LoginType = 'S'
                   BEGIN
                       SET @LoginPWD = CAST(LOGINPROPERTY(@LoginName, 'PasswordHash') AS VARBINARY(256))
                       EXEC [master].[dbo].[sp_hexadecimal] @LoginPWD, @PWD_String OUT    
                       EXEC [master].[dbo].[sp_hexadecimal] @LoginSID, @SID_String OUT
                       SELECT @is_policy_checked = S.is_policy_checked
                           , @is_expiration_checked = S.is_expiration_checked
                       FROM sys.sql_logins S  
                       WHERE S.[type] = 'S' AND  S.[is_disabled] =0  
               
                       -- Create Script
                       SET @Script = @Script + CHAR(13) 
                           + CHAR(13) + '''' 
                           + CHAR(13) + 'USE  [master];'  + CHAR(13) 
                           + 'IF EXISTS (SELECT name FROM sys.server_principals WHERE name= ''''' + @LoginName + ''''') ' 
                           + CHAR(13) + 'BEGIN '
                           + CHAR(13) + CHAR(9) + ' DECLARE @CurrentLoginPWD VARBINARY(512)'
                           + CHAR(13) + CHAR(9) + ' DECLARE @CurrentPWD_String VARCHAR(1514)'
                           + CHAR(13) + CHAR(9) + ' DECLARE @CurrentLoginSID VARBINARY(400)'
                           + CHAR(13) + CHAR(9) + ' DECLARE @CurrentSID_String VARCHAR(1514)'
                           + CHAR(13) + CHAR(9) + ' SELECT @CurrentLoginSID = [sid]  FROM sys.server_principals WHERE name = '''''+ @LoginName +''''''
                           + CHAR(13) + CHAR(9) + ' SET  @CurrentLoginPWD =CAST(LOGINPROPERTY(''''' + @LoginName + ''''', ' + '''''PasswordHash''''' +') AS VARBINARY(512))'
                           + CHAR(13) + CHAR(9) + ' EXEC [master].[dbo].[sp_hexadecimal] @CurrentLoginPWD , @CurrentPWD_String OUT    '
                           + CHAR(13) + CHAR(9) + '
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • LeetCode279:完全平方數，動態規劃解法超過46%，作弊解法卻超過97%
    歡迎訪問我的GitHub 這裡分類和彙總了欣宸的全部原創(含配套源碼)：https://github.com/zq2599/blog_demos 本篇概覽 本篇概覽 這是道高頻面試題，值得一看 首先，這道題的難度是中等 來看題目描述： 給你一個整數 n ，返回 和為 n 的完全平方數的最少數量 。 ...
  • 淺談“文件與文件流”的區別
    文件與文件流的區別（自己的話）： 在軟體開發過程中，我們常常把文件的“讀寫操作”，與“創造、移動、複製、刪除操作”區分開來。其中，文件的“讀寫操作”是由System.IO命名空間下fileStream類控制的。而文件的“創造、移動、複製、刪除操作”是由file類控制的。 當然了，還有很多和這兩個類功 ...
  • 如何保持 SSH 會話不中斷？
    哈嘍大家好，我是鹹魚 不知道小伙伴們有沒有遇到過下麵的情況： 使用終端（XShell、secureCRT 或 MobaXterm 等）登錄 Linux 伺服器之後如果有一段時間沒有進行交互，SSH 會話就會斷開 如果正在執行一些非後臺命令，SSH 會話斷開就可能會使得這些命令可能會被中斷，導致任務無 ...
  • 全局安裝oh-my-zsh保姆教程
    我的系統是CentOS 7.6，按流程走完後可以實現系統內所有用戶都預設使用zsh且插件配置共用省去重覆編寫配置或軟連接的煩惱 1 安裝git yum -y install git 2 安裝zsh yum -y install zsh 3 更改root的預設shell #查看當前可用shell列表 ...
  • 解密Linux中的通用塊層：加速存儲系統，提升系統性能
    本文探討了Linux操作系統中的通用塊層和存儲系統I/O軟體分層的優化策略。通用塊層作為文件系統和磁碟驅動之間的介面，通過排隊和調度I/O請求，提高磁碟的讀寫效率和可靠性。存儲系統的I/O軟體分層包括文件系統層、通用塊層和設備層，它們相互協作，實現對存儲系統的高效管理和操作。本文旨在深入瞭解通用塊層... ...
  • 不同CPU性能大PK
    前言 比較Hygon7280、Intel、AMD、鯤鵬920、飛騰2500的性能情況 CPU型號 Hygon 7280 AMD 7H12 AMD 7T83 Intel 8163 鯤鵬920 飛騰2500 倚天710 物理核數 32 32 64 24 48 64 128core 超線程 2 2 2 2 ...
  • 讀高性能MySQL（第4版）筆記06_優化數據類型（上）
    1. 良好的邏輯設計和物理設計是高性能的基石 1.1. 反範式的schema可以加速某些類型的查詢，但同時可能減慢其他類型的查詢 1.2. 添加計數器和彙總表是一個優化查詢的好方法，但它們的維護成本可能很 1.3. 將修改schema作為一個常見事件來規劃 2. 讓事情儘可能小而簡單是一個好主意 2 ...
  • ORACLE的Scheduler的時區總結
    在Oracle資料庫中，一般人們可能只會關註資料庫的時區或會話的時區，但是很少有人會關註Scheduler的時區設置,因為Scheduler的時區設置一般只會影響作業的scheduler和Windows的運行，很多時候，人們往往沒有註意到它。這裡簡單總結一下這個知識點。 在Oracle 10g/11 ...
一周排行
    -Advertisement-
    Play Games
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類