Shulou(Shulou.com)06/01 Report--

Migration of Login between SQL Server database instances

1. Popular method: T-SQL

The old way is to prepare the CREATE LOGIN script, fill in the account and password, keep the SID consistent, and execute it on the new server instance.

Microsoft also provides two stored procedures in KB918992 and KB246133 to solve the problem of Login migration between various versions. Different versions use different schemes.

Note that there are two versions of the password hash:

VERSION_SHA1: use the hash generated by the SHA1 algorithm for SQL Server 2000 to SQL Server 2008 R2.

VERSION_SHA2: a hash generated using the SHA2 512 algorithm for SQL Server 2012 to later versions.

For SQL Server 2012 and later, you need to manually modify the length of the hash variable in the KB918992 script:

Replace 256 with 512

Replace 514 with 1028

Replace 1024 with 2048

Example syntax:

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

Sample output:

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

two。 Less used method: SSIS's Transfer Logins Task

SQL Server Data Tools provides Transfer Logins Task tasks, and we can migrate all Logins, a Login, and select a specific Logins collection. We can also configure to overwrite, skip, or throw errors when Login exists. See the Technet article for details.

3. Trendy methods: Powershell's Copy-SqlLogin and Export-SqlLogin

Copy-SqlLogin and Export-SqlLogin are not the cmdlet of Microsoft's official SQL Server module, so you need to install the dbatools module first:

Http://dbatools.io/getting-started

Then perform a Login export:

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

By default, Export-SqlLogin exports all Login, or you can choose to include or exclude Login.

Or do you want to dynamically migrate Login to SQL Server 2016 from SQL Server 2000?

Copy-SqlLogin-Source sqlsvr2000-Destination newsql2016

Copy-SqlLogin can run from SQL Server 2000 to 2016.

What do I do if I want to synchronize the Login permissions of the availability group? The dbatools module provides Sync-SqlLoginPermissions cmdlet to do the job. Unlike Copy-SqlLogin,Sync-SqlLoginPermissions, new Login is not added. It simply synchronizes the collection of instance and database permissions, as well as instance and database roles and job owners.

Sync-SqlLoginPermissions-Source sql2005-Destination sql2016

What do I do if I want to copy the jobs of the availability group? You can use Copy-SqlJob.

Reference:

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/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.