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?

| improve this question | | | | |
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.

| improve this answer | | | | |
  • 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 says try topanswers.xyz 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 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 Aug 18 '15 at 9:09
6

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-DbaLogin -Source sqlsvr2000 -Destination newsql2016

or:

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

Cheers!

| improve this answer | | | | |
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
| improve this answer | | | | |
0

Yes you Can through Powershell using various Modules:

1- Using DBATools: Open Powershell

<# Install-Module dbatools#> #only if not installed

<# Load the module#>

Import-module dbatools

export-dbalogin -SqlInstance ServerName f:\floginsdba.txt

2- Using SQLSERVER Module:

<# Install-Module sqlserver#> #only if not installed

<# Load the module#>

Import-module sqlserver

cd sqlserver:\serverA\default\logins   # replace the default with any named Inst

dir | %{$_.script()}                   

<# For security, it will create disabled  logins and hashed passwords for sql logins.#>

Let me know if you need any help.

| improve this answer | | | | |

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.