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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類