SQL Server數(shù)據(jù)庫實例間遷移Login

SQL Server數(shù)據(jù)庫實例間遷移Login

1. 流行的方法：T-SQL

老式的方法是，準備好CREATE LOGIN腳本，填入賬號和密碼，保持SID一致，在新服務器實例上執(zhí)行。

微軟在KB918992和KB246133也提供了2個存儲過程來解決各種版本間Login遷移問題。不同版本使用不同的方案。

注意密碼的哈希有以下兩個版本：

• VERSION_SHA1：使用SHA1算法產(chǎn)生的哈希，用于SQL Server 2000到SQL Server 2008 R2。

• VERSION_SHA2：使用SHA2 512算法產(chǎn)生的哈希，用于SQL Server 2012到之后的版本。

對于SQL Server 2012及之后的版本，需要手工修改KB918992腳本中哈希變量長度：

• 替換256為512

• 替換514為1028

• 替換1024為2048

示例語法：

EXEC dbo.sp_help_revlogin
--OR
EXEC dbo.sp_help_revlogin @login_name = 'BlogTester' -- sysname

示例輸出：

/* sp_help_revlogin script
** Generated Aug 20 2012 8:24AM on V-DEV-DB-011\vb18 */
-- Login: BlogTester
CREATE LOGIN [BlogTester] WITH PASSWORD = 0x01000D1F43BB2A1F306EC90F5352291E8DD273549DB4AF950845 HASHED, SID = 0xD831296B0274D448A5748A52B8C796EA, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

2. 少用的方法：SSIS的Transfer Logins Task

SQL Server Data Tools提供了Transfer Logins Task任務，我們可以遷移所有Logins，一個Login，選擇特定的Logins集合。我們也可以配置當Login存在的時候覆蓋、跳過或拋出錯誤。詳見Technet文章。

3. 新潮的方法：Powershell的Copy-SqlLogin和Export-SqlLogin

Copy-SqlLogin和Export-SqlLogin并不是微軟官方的SQL Server模塊的cmdlet，因此，你先要安裝dbatools模塊：

http://dbatools.io/getting-started

然后執(zhí)行Login導出：

Export-SqlLogin -SqlServer sql2005 -FileName C:\temp\sql2005-logins.sql

默認，Export-SqlLogin導出所有的Login，也也可以選擇包含或排除的Login。

或者，你想從SQL Server 2000動態(tài)遷移Login到SQL Server 2016？

Copy-SqlLogin -Source sqlsvr2000 -Destination newsql2016

Copy-SqlLogin可以運行在SQL Server 2000到2016。

如果想同步可用性組的Login權限，怎么做呢？dbatools模塊提供了Sync-SqlLoginPermissions cmdlet來做這個工作。不像Copy-SqlLogin，Sync-SqlLoginPermissions不添加新Login。它只是同步實例和數(shù)據(jù)庫權限集合，以及實例和數(shù)據(jù)庫角色和作業(yè)所有者。

Sync-SqlLoginPermissions -Source sql2005 -Destination sql2016

如果想拷貝可用性組的作業(yè)，怎么做呢？可以使用Copy-SqlJob。

參考：

http://www.sqlhammer.com/how-to-transfer-logins-to-a-new-server/

http://dba.stackexchange.com/questions/63518/how-do-i-transfer-logins-from-2008r2-to-2014-with-passwords

https://blog.netnerds.net/2016/06/its-2016-why-is-sp_help_revlogin-a-thing/