五餅二魚工作室

最近在處理一個 SQL Server，這個 SQL Server 原本是建立在 Azure 上用來測試的 SQL Server VM，因為一開始只是用個幾天的測試環境，因此並沒有太多的去規劃 VM 的配置和規模，但後來因為需要長期使用，才發現該 Azure 的 VM 每個月都需要花到約 15000元，因此需要重新將該 VM 選擇不同的設定和訂閱，去重新建立一個新的測試環境。

看起來這種環境上的移轉，應該是個簡單任務，而剛好有朋友也有類似相同的問題，只是他的是要把地端的 SQL Server 移植到 Azure，也需要相關帳號的移轉，因此就想說來整理一下相關的指令。

首先我們先針對 SQL 認證的帳號來做處理，這些帳號資料可以透過 sys.sql_logins 這個 DMV 來取得相關的帳號、SID、加密的密碼值，因此我們就可以透過這些資訊轉換成為 CREATE LOGIN 的指令。

因此我們就可以用以下的 SQL 指令來取得我們需要的資訊

select name,sid,type,is_disabled,password_hash 
from sys.sql_logins
where type = 'S' AND is_disabled = 0 

早期如果帳號不多，我多半自己用 Copy/Paste 的方式將這些 sid 和 password_hash 的值，一個一個複製出來組成 CREATE LOGIN 的指令。但是最近遇到的案例都有數十個帳號，自己慢慢的在那複製一來感覺蠻蠢的，二來帳號那麼多又擔心自己複製錯誤，因此我們就想試試看，有沒有辦法直接將上述資料轉為對應的語法。

基本上想起來是很容易，但是 sid 和 password_hash 這兩個欄位都是二進位型態的欄位，那我們要怎麼將這個轉為字串來做處理呢 ? 這裡我們會用到 convert 指令中比較少用到的參數來做處理。一般我們看到 convert 的函數的時候，很高的比例都是在日期和字串之間的轉換，但是當我們要轉換二進位為字串的時候，這個函數也是非常有的。

下面是微軟 Convert 指令的說明，中間有個部份說明當我們需要將二進位轉成字串的格式來呈現的時候，可以在第三個參數設定為 1，就可以有我們想要的效果了。

所以我們就可以將語法調整為以下的方式

select 
	'CREATE LOGIN ['+name+'] WITH PASSWORD = ' + convert(varchar(256),password_hash,1  ) + ' HASHED , SID = ' + convert(varchar(256),sid,1  )
from sys.sql_logins　
where type = 'S' AND is_disabled = 0 

這樣輸出的結果就是我們預期的結果了

CREATE LOGIN [abc] WITH PASSWORD = 0x0200A081606CB6BF528B9A1F9AE9C1D1D0F5418B9E7A40099E7C6A1A3025AC09A841EF468FDD5DF8DB5D9127CBAE62B4FA137C02A32885DE66CCBD579343A1CB9A97EE3EC277 HASHED , SID = 0x3349687F5C1BBA43B16EBA18BA12

CREATE LOGIN [xyz] WITH PASSWORD = 0x0200077EAD6F976A03019F9CB43BFB2E47C9D30C6E0819C39DA0A3BDF51BA5B9B4F4EA5D9230625117E3DEBAD77F7CC8409A59D243D3D18A0E66A7FB1FE8A9D51D00296C4C2D HASHED , SID = 0xB9D067003B84B94E818884FC26B1

---

透過上述的指令，我們已經可以將 SQL 認證的帳號給匯出了，然而這只是我們所要的一部分，還有一部分是關於伺服器的角色。這裡我們就不討論資料庫角色，因為當我們要移轉 Server 的時候，使用者和其資料庫角色的設定，都會伴隨著放在資料庫裏面，因此我們只要處理伺服器角色就可以了。這裡我們將透過 ALTER SERVER ROLE 指令的相關參數，將相關設定轉為 SQL 指令，就可以方便我們移轉的時候不會遺失相關角色的設定了。

這裡我們主要要使用 sys.server_role_members  這個 DMV，他可以取出伺服器角色有哪些成為，只是那些設定都只有 id ，因此我們需要再去跟 sys.server_principals 這個 DMV 去關聯，就可以組合出上述 ALTER SERVER ROLE 的語法，因此我們就完成以下的指令

select r.name as Role, m.name as Principal
from sys.server_role_members rm
inner join sys.server_principals r on rm.role_principal_id = r.principal_id and r.type = 'R'
inner join sys.server_principals m on rm.member_principal_id = m.principal_id and m.principal_id > 1

因此從上述結果看起來，除了內建的相關帳號具有的伺服器角色外，我們可以取得原本該伺服器相關登入帳號的角色，因此再透過 SQL 指令的調整，我們可以整理出以下的語法

select 
  'ALTER SERVER ROLE ['+r.name+'] ADD MEMBER ['+ m.name +']'
from sys.server_role_members rm
inner join sys.server_principals r on rm.role_principal_id = r.principal_id and r.type = 'R'
inner join sys.server_principals m on rm.member_principal_id = m.principal_id and m.principal_id >= 270

並且透過該語法，就可以將我們原本在主機上所設定的伺服器角色的設定，轉換成為 SQL 語法

ALTER SERVER ROLE [serveradmin] ADD MEMBER [abc]
ALTER SERVER ROLE [dbcreator] ADD MEMBER [xyz]

---

因此在整個過程中，我們只要善用幾個方便的 DMV ，就可以很容易的來移轉 SQL Server 相關的登入帳號和伺服器角色，這樣當我們遇到 Server 要升級或者是要移轉的時候，就會相段的容易許多了。

PS. 2023/02/02 謝謝好友的提醒 , 轉換 SID 的部分漏加上長度 , 造成轉出 SID 變成字串的時候會有部分被截斷 , 目前已經修正 Script