SQL Server数据库实例间迁移Login

SQL Server数据库实例间迁移Login

 

1. 流行的方法：T-SQL

老式的方法是，准备好CREATE LOGIN脚本，填入账号和密码，保持SID一致，在新服务器实例上执行。

微软在KB918992和KB246133也提供了2个存储过程来解决各种版本间Login迁移问题。不同版本使用不同的方案。

注意密码的哈希有以下两个版本：

• VERSION_SHA1：使用SHA1算法产生的哈希，用于SQL Server 2000到SQL Server 2008 R2。

• VERSION_SHA2：使用SHA2 512算法产生的哈希，用于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

然后执行Login导出：

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

默认，Export-SqlLogin导出所有的Login，也也可以选择包含或排除的Login。

或者，你想从SQL Server 2000动态迁移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。它只是同步实例和数据库权限集合，以及实例和数据库角色和作业所有者。

Sync-SqlLoginPermissions -Source sql2005 -Destination sql2016

如果想拷贝可用性组的作业，怎么做呢？可以使用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/