12

I've tried Tasks>Generate Scripts... and set 'Script Logins' in the Advanced Scripting Options, but I get this in the output:

/* For security reasons the login is created disabled and with a random password. */

Is there a simple way to script these logins with passwords or another way to transfer logins from a 2008R2 instance to a 2014 instance?

  • 4
    sqlsoldier.com/wp/sqlserver/… (just ignore the mirroring references - this works for moving any logins between any instances). – Aaron Bertrand Apr 19 '14 at 17:27
  • @Aaron that looks very nice at first glance, thanks. – Jack Douglas Apr 19 '14 at 17:31
  • 1
    @Aaron I ended up using this which is basically the same thing. Thanks for pointing me in the right direction. – Jack Douglas Apr 19 '14 at 21:33
8

In order to transfer logins, you will need to use the sp_help_revlogin script and procedure. This will allow you to transfer logins and retain the password from on instance to another.

  • I've been to that page but I evidently don't understand it. Method 3 says "Create a log in script that has a blank password", so presumably that isn't it? – Jack Douglas Apr 19 '14 at 17:08
  • 1
    @JackDouglas Not sure what that phrase means, but you just need to create those two stored procedures on the source server and, in the end, just execute sp_help_revlogin, take its output from SSMS and execute it on the new server. You'll have your logins recreated with the correct pwds. – Marian Apr 20 '14 at 15:30
  • i have tested this on SQL 2014 but does not work, the same method works till sql 2012. also mentioned in support.microsoft.com/kb/91899 – Iman Abidi Aug 18 '15 at 8:57
  • I tested sp_help_revlogin but it does not work in SQL 2014 SP1 CU1(12.0.4416). This KB article mentions that it works until SQL Server 2012. – Iman Abidi Aug 18 '15 at 9:09
5

I can't wait for everyone to learn about this:

https://dbatools.io/

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

In short: install the dbatools Powershell scripts.

You now have a wonderful set of commands that can not only copy SQL users, passwords, permissions, etc; but also an exhaustive set of commands for copying databases, jobs, even whole instances :D

Example usage:

Copy-SqlLogin -Source sqlsvr2000 -Destination newsql2016

or:

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

Cheers!

1

you can still use sp_help_revlogin script and procedure for SQL 2014 as answered earlier (How to transfer logins and passwords between instances of SQL Server). But it needs manual modifications (enlarging length of hash value variables):

  • replace 256 -> 512
  • replace 514 -> 1028
  • replace 1024 -> 2048

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.