It’s 2016, so why are you still using sp_help_revlogin?


Today’s blog post is part of T-SQL Tuesday. T-SQL Tuesday is the brainchild of Adam Machanic. It is a monthly blog party on the second Tuesday of each month. Everyone is welcome to participate.

sp_help_revlogin is now old enough to drive a car

By now in your career, you may have needed to migrate a few databases and logins. The process isn’t as bad as it could be, after all Microsoft published sp_help_revlogin over 15 years ago to help migrate logins with their passwords and SIDS.

But by using this method, you are required to do the following, as demonstrated in the painfully boring 41 second video below:

  1. Find the stored procedure somewhere on your hard drive/the Internet
  2. Install sp_help_revlogin and sp_hexadecimal on the source server
  3. Execute it on the source server
  4. Copy the resulting SQL output
  5. Paste it into the query pane of the resulting server
  6. Execute

Now you’ve migrated the logins with their passwords, SIDs, and a few default properties. But you don’t have the logins’ server roles, server permission sets, database roles or database permission sets. Now you must find and use someone’s modified version of sp_help_revlogin, but you’re still left with manually executing the procedure against your source and destination servers.

Let’s hope you only have one or two SQL Servers to migrate and not hundreds.

Oh, and don’t forget different versions of SQL Server use different hashing algorithms, so you’ll need to use one of the many different versions of sp_help_revlogin if you have a mixed environment.

Introducing Copy-DbaLogin and Export-DbaLogin

Copy-DbaLogin and Export-DbaLogin are the PowerShell answers to performing SQL Server login migrations. You may have heard of or even used PowerShell for your DBA tasks, but these are especially exciting times for the SQL and PowerShell community.

It’s no secret that prior to SQL Server 2016, the SQL Server team didn’t invest much in PowerShell. SQLPS was slow, rude, and didn’t conform to standard PowerShell practices. The SQL Server community was given a module with less than 50 commands, while the Lync, Exchange and SharePoint communities have been bestowed over 700 commands each.

But all of that has changed changed. Microsoft now has a dedicated SQL PowerShell engineer and the SQL Server Tools team is actively asking for community feedback. Just check out the exciting things going on with the soon-to-be-released cmdlet, Get-SqlErrorLog.

Copy-DbaLogin and Export-DbaLogin are not part of SQLPS, but it does help illustrate how PowerShell can make our lives easier. I am a Microsoft PowerShell MVP, but I’ve also been a SQL Server DBA since 1999. I’ve done a ton of migrations, but they’ve always been such a pain. Until now.

Want to export all of your logins to a file, complete with SID, hashed password, roles, permission sets, securables and all that? Just install the dbatools module then execute

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

Or what if you want to do a live login migration from SQL Server 2000 to SQL Server 2016? I’ve done it a ton of times (in a lab of course. I don’t run such old versions at work;).

Copy-DbaLogin -Source sqlsvr2000 -Destination newsql2016

Copy-DbaLogin works on SQL Server version 2000-2016. It takes care of the hashing algorithm differences. It works on clusters, named instances, all editions from Express to Enterprise and it copies both Windows and SQL Logins. It copies the default database, default language, password policy settings and so on. And it does so in just a few seconds. Check out this magical and exciting video.


Need to sync your login permissions for your ags? Sync-DbaLoginPermission was made just for that. Unlike Copy-DbaLogin, Sync-DbaLoginPermission doesn’t add new logins. It just syncs the server & db permission sets, as well as the server & db roles and job ownership.

Sync-DbaLoginPermission -Source sql2005 -Destination sql2016

Need to copy jobs for you availability groups, too? Copy-SqlJob has you covered.

Convinced yet?

If you’ve been a fan of PowerShell, I hope the SQL Server team’s recent dedication to PowerShell renews your excitement. If you’re a PowerShell holdout, I understand. I adopted PowerShell for SharePoint long before I did PowerShell for SQL Server. I thought it was slow (have you seen the performance of Start PowerShell in SSMS prior to 2016?!) and hard to learn. But now I see its power and it will only get more powerful now that it has a dedicated resource.

If you still need convincing, check out for some fun and powerful commands like Reset-DbaAdmin, Get-DbaProductKey and Set-DbaMaxMemory.

Thanks for joining me for this month’s T-SQL Tuesday!

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server
56 comments on “It’s 2016, so why are you still using sp_help_revlogin?
  1. Derek says:

    Very cool, I didn’t realize the powershell scripts existed for copying logins.

  2. AndyWawa says:

    Hello Chrissy,
    thanks for your tremendous job writing all the scripts to migrate the sql servers! It’s a real time/trouble -and anger saver!
    One question about the “Copy-SqlLogin ” script.
    I’m testing it on my local PC (Win 10) having 2 SQL Instances: HP_PC\DEV2012 and HP_PC\2014, the PC-Name (as you can guess) is: HP_PC. There is a local Win-account: HP_PC\andy. I create a new login on the DEV2012 instance. Then running the script I get an error message saying: “HP_PC\andy is skipped because it is a local machine name.”. I found the place in your script (Copy-SqlLogin.ps1) beginning at the line: 153 taking care about this error. Am I doing something or did I get something wrong?
    Thanks for your reply.

    • Chrissy LeMaire says:

      It’s not you, it’s the script and my expectations of how people will use the script. One time, I used an alias and accidentally dropped all my databases ;) So I protected myself from me. You can remove those lines that prevent you from migrating local accounts, and I’ll update the script shortly.

  3. Anne Evert-Nielsen says:

    Hi Chrissy,

    You have done a tremendous job, which saved me many hours of tedeous work.
    Thanks a lot. If you ever need good references, you can count on me.
    Yours sincerely
    Anne Evert

  4. Lucian says:

    I cannot copy a single database. Somehow the -Database parameter does not work. If left out the ErrorId says “You must specify a -AllDatabases or -Database to continue.”

    • Chrissy LeMaire says:

      Hey Lucian,
      That means that the script can’t login to your Source server to compile the list of databases. Try Test-SqlConnection to see what the error is.

      • Lucian says:

        Thanks, there was a problem with the account I used. I was just about to post it. THANKS a lot for this. I will test some of the scripts that I will use. THIS is really awesome stuff.

  5. Hi Chrissy LeMaire,

    The script and method you provided are simply perfect they are effortless, thank you very much for this.

    I will explore the solutions later what i loved is the way we can get all scripts in a file and execute later on, thanks a ton.


  6. Michelle says:

    Hi Chrissy,

    Thanks for posting this. I have have a number of SQL servers and I am able to make your script work on the ones using the default SQL port, but not on the ones that do not use the default port. I’ve tried adding the port to the server name, (servername:1234 for example) but that doesn’t work either. The Test-SqlConnection command says “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)” I’m pretty sure the issue is the non-standard port. What can I do to have the script change the port number it looks at?


    • Chrissy LeMaire says:

      Hey Michelle! SQL Server connection strings require commas, unfortunately. It’s also my instinct to use a colon, so I’ll see what I can do about handling this. But for now, it would be Test-SqlConnection -SqlServer ‘sql2014,1433’

      • Michelle says:

        I actually tried it with the comma, but not with the quotes. Now that I’m using quotes, it’s working!

        Thanks so much!

  7. Oscar says:

    Hello Chrissy,

    First, thank you very much for those amazing scripts.

    I have a problem trying to migrate a sql login from SQL2000 to SQL2016,I am getting this error on the dbatools-exceptions log file:

    System.Data.SqlClient.SqlException: Invalid value given for parameter PASSWORD. Specify a valid parameter value.

    Seems to be an error with the hashed algorithm for the password, Do you know how can I solved this?

    Thanks in advance.

  8. DBA Matt says:

    Hello, I have the following error, if it’s possible to point me in the right direction:

    PS C:\git\dbatools> Export-SqlLogin -SqlServer MSSQLSERVER2016 -FilePath C:\SQLLogins\logins.sql
    You cannot call a method on a null-valued expression.
    At C:\Users\matthew.stephens\Documents\WindowsPowerShell\Modules\dbatools\functions\Export-SqlLogin.ps1:118 char:4
    + $null = $allparams.Add(“Databases”, $dbparams.Databases)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    • Chrissy LeMaire says:

      Interesting! Modify the file C:\Users\matthew.stephens\Documents\WindowsPowerShell\Modules\dbatools\functions\Export-SqlLogin.ps1 and remove the line that starts with DynamicParam. We’ll get that fixed in the next release.

      • DBA Matt says:

        Thanks – now I just have connection issues to try and overcome :(

      • DBA Matt says:

        I seem to still have a connection error. When I run the code below to test the connection with just the instance name or the servername/instance name it comes back successful, but when I run the export code I get an error. Help help much appreciated

        Code to test SQL connection:
        [String] $Server

        $connectionString = “Data Source=$Server;Integrated Security=true;Initial Catalog=master;Connect Timeout=10;”
        $sqlConn = new-object (“Data.SqlClient.SqlConnection”) $connectionString
        Write-Error “Cannot connect to $Server.”;

        if ($sqlConn.State -eq ‘Open’)
        “Opened successfully.”

        Code running for export:
        Export-SqlLogin -SqlServer MSSQLSERVER2016 -FileName C:\SQLLogins\sql2016-logins.sql


  9. AndyWawa says:

    I’ve just tested the function Export-SqlLogin, it seems to work.
    I’m afraid you’re calling the function wrongly.
    You use the syntax: Export-SqlLogin -SqlServer MSSQLSERVER2016 -FilePath and get the error: “You cannot call a method on a null-valued expression.”, means: you didn’t provide the instance name (probably?). I’m calling the function: Export-SqlLogin -SqlServer HP_XXXXX\DEV2014 -FilePath “C:\temp\logins.sql” and it works. Having a default instance try YourServer\MSSQLSERVER, or maybe (just for test) only the IP-address.
    @Chrissy: maybe we should change the example in function-help, now we have: Export-SqlLogin -SqlServer sql2005 -FilePath C:\temp\sql2005-logins.sql and it won’t work,I think…

  10. Sunny Wong says:

    Hi, I’m trying to install the DBAtools. But my server does not has internet access. Pls advise how can I just download the required files and install DBAtools on my server.


  11. Chris says:

    Can you use this to copy NT logins between sql servers? I dug around through the commands and didn’t see anything. We are looking to leverage this awesome tool to copy sql and NT logins through our Always On cluster.

    • Chrissy LeMaire says:

      Sure can! Works for SQL logins and Windows Logins.

      • Chris says:

        Great, one more question for you. I have a need to keep staging and dev environment DB’s up to date daily.

        It looks like I could schedule a task that would run the following and that would take care of it.

        copy-sqldatabase -source prod -destination dev -all databases -force

        Just want to make sure that is the proper way to do that.


  12. Vicky Richardson says:

    Hi, I’ve just tried to run Copy-SqlLogin and received the error “An exception occurred while executing a Transact-SQL statement or batch. —> System.Data.SqlClient.SqlException: Invalid value given for parameter PASSWORD. Specify a valid parameter value.”. Source and destination servers are both running SQL 2014 with SP2. I know some of the SQL logins have been around since we were on SQL 2000. is there anything I can do to correct this error?

  13. IJeb Reitsma says:

    I need to copy just logins, and have no need for ‘CREATE USER …’ and database permissions of users.

    This because I migrate a database by backup/restore, which takes care of the users and the permissions inside the database.

    I don’t want to take risks with changing permissions inside other databases which were migrated to the destination server before.

    It seems this is not possible with Copy-SqlLogin or am I missing something?
    Or should I change the code to accomplish this?

  14. Howard Long says:

    I’d love to use tools like this, but in locked down Enterprise level environments, you need to be able to deploy offline, and not have any prerequisites. As far as I can see, there is no documented method of doing offline installs.

    • Howard Long says:

      I take it back, there _is_ and offline install. It’s a bit slow exporting logins though, only about one every 2 secs.

  15. Gary says:

    AWESOME!!!! This is the best find of the year, the decade even! Am loving these new tools!

  16. Paul says:

    I take it this does not work of you want to script out Logins for an Azure SQL Master Database? This is what I am trying to achieve.

    • Chrissy LeMaire says:

      I don’t use Azure so I’m not sure, however, please try it. If it doesn’t work, file an issue at and we’ll take a look.

  17. Roni Vered Adar says:

    Thanks for the post,
    That’s indeed a great improvement.
    I wonder how come no one automated it before DBAtools

  18. mb says:

    This is great stuff, but still begs the question of why MS devs still have their heads planted firmly where the sun don’t shine. Why the *ck doesn’t SQL Server just handle replication of login information natively?

  19. Billy says:

    Hi Chrissy,

    Thanks for your effort, when I was trying to use Copy-DbaLogin command, it shows the “Object reference not set to an instance of an object.” error, so I wonder what I can do to fix this issue, thanks a lot!

  20. Kenneth says:


    I am getting the error below whenever I try to migrate the login. I am running it on a desktop running windows 8 and using IP address to define the source and destination. Thank you.

    PS C:\Users\knwang\Documents\WindowsPowerShell\Modules\dbatools> Copy-SqlLogin -Source -Destination 192.16
    8.24.27 -Login migrateme
    Object reference not set to an instance of an object.
    At C:\Users\knwang\Documents\WindowsPowerShell\Modules\dbatools\allcommands.ps1:9553 char:13
    + Copy-Login -sourceserver $sourceServer -destserver $destServer -Logi …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (:) [], NullReferenceException
    + FullyQualifiedErrorId : System.NullReferenceException

  21. Shrikanth says:

    Hi Chrissy, Thanks for all the hard work/giving it back to all the other DBA’s is a wonderful work. Hats-off to you/all your team. I was using Export-DbaLogin and Copy-DbaLogin and all other functions – Incredible work.
    I may be wrong, i was not able to find a cmdlet which does create missing database roles/obj level permissions between two databases. Do we have in dbatools? Appreciate all your hard work. At Server level, if the roles are not sync, do we have cmdlet to sync if mismatch?

  22. Faisal says:

    Is there a way to just export the logins/permissions to a file and then import on the target from the file? I’m upgrading from SQL 2008R2 cluster to SQL 2016 cluster. however, the SAN team will just move the storage from current environment to new (after taking the source offline, of course). and then I’ll install SQL and then attach the datbases. so both the source and target will NOT be online at the same time.

  23. Michael Werts says:

    I have a question about the sp_help_revlogin procedure. I know this generates an encrypted version of the original password for SQL Logins, but I always thought this would only work on a restored master database of the original server. Is there anything that would prevent a hacker from restoring the original master database (or creating a new one), and running this procedure, plus running sp_change_users_login, to gain whatever access is granted to the SQL LoginS?

    I know if a hacker is able to nab a copy of the master that would be a big enough problem in the first place, but I figure I would ask…

  24. Ron Sexton says:

    Interesting work and it is a great help.
    It does, however, show how much admin capabilities are missing in SQL Server. It’s surprising MS hasn’t really done more about this.
    I see there is a lot more a product could do as well.
    Having used sp_help_revlogin before many times, i am grateful for a more up to date replacement and appreciate all your hard work in your ‘free’ time! ;)

  25. Steven Vallarian says:

    So this is Export-DbaLogin now?

  26. David says:

    Thanks a million for that Module Chrissy!

    However when I try to copy a login(copy-dbalogin) for a database that is part of an AG group. It doesn’t replicate the database level permission ((doesn’t create user), even if my destination server is the primary replica.

    I also tried sync-dbaloginpermission without success. It only replicate the user for database that are not part of an AG group.

    My source server is SQL Server 2012 and my destination server is SQL Server 2017. What am I doing wrong? Please assist

  27. Rich says:

    Looking at the resultant set of SQL code and Powershell these commands don’t synchronize existing user passwords. Is there a way to do that?

  28. Kincho says:

    “It’s 2016, so why are you still using sp_help_revlogin?”

    Haha.. that would be like asking it’s 2019, why are you still listening to Vinyl records! New is not always better!

    • Chrissy LeMaire says:

      New is not always better but the PowerShell solution is far faster, more thorough and objectively better if you can run PS within your org.

      That said, I appreciate freedom of choice. Enjoy the sproc and the vinyl, bud.

  29. Bill says:

    Hi Crissy,

    Can you use the copy SQL logins to Servers across domains? I have a different user account in each domain

  30. Richard says:

    It’s a great command, but it doesn’t deal with all securables.
    e.g. “GRANT IMPERSONATE ON LOGIN::[blah] TO [blah2]”

  31. Andy Levy says:

    A dbatools user has pointed out to me that the function names are now `Copy-DbaLogin` and `Export-DbaLogin`. This was part of the 1.0 launch and I totally understand the why & how, he was just confused by `Copy-SqlLogin` not working on his current version of dbatools.

2 Pings/Trackbacks for "It’s 2016, so why are you still using sp_help_revlogin?"
  1. […] Create logins. Chick here to get details of power shell script to create logins. […]

Leave a Reply to Oscar Cancel reply