1

We have a single SQL server with about 60 databases running on Win2012r2 with SQL 2014.

I am in the process of planning a hardware refresh and plan to migrate to WIN2016 with SQL 2017, I understand the basics of migrating to a new server (copy files over, re-attach, test etc).

My question is, we have specific restricted logins setup for each database. The logins are windows ad groups and have specific permissions for their allowed databases. In addition to this we have about 20 SQL logins with the same setup against their databases.

My question is, how can i migrate these logins and databases while maintaining the permissions for specific databases?

  • There is also the Data Migration assistant tool from Microsoft. It will do everything your asking and is pretty awesome. I have used it to migrate 100+ db Instance from about the same specs as yours. Of course, learning to script the logins is equally important! – clifton_h Mar 2 at 2:17
2

I would use Copy-DbaLogin from the dbatools powershell module.

This command migrates the logins from server A to server B.

From their website the example:

Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Force

Copies the logins from sqlserver2014a to sqlcluster and overwrites them if they exist by using the -Force parameter

What does it migrate?

Migrates logins with SIDs, passwords, defaultdb, server roles & securables, database permissions & securables, login attributes (enforce password policy, expiration, etc.)

Same source as the one mentioned above

0

I think you can use this if you are not familiarized or not allowed to run with Powershell(Dbatools): https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

Some of us are still using it

Executing stored procedure [sp_help_revlogin] on old instance will generate a windows and sql login script with sid that will fix orphan users and users will get the same permission as old instance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.