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

TSQL2SDAY-150x150

This month’s T-SQL Tuesday, hosted by Michael Swart (blog | twitter), is all about SQL Server 2016.

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. Unfortunately, there has been no notable progress since this stored procedure was first released.

Why is this an issue? Well, by using sp_help_revlogin, 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. So now you gotta 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.

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.

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

Introducing Copy-SqlLogin and Export-SqlLogin

Copy-SqlLogin and Export-SqlLogin are the PowerShell answers to performing SQL Server login migrations. These commands are not part of Microsoft’ official SQL Server module, SQLPS, but they do help illustrate how PowerShell can make our lives easier.

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-SqlLogin -SqlServer sql2005 -FileName C:\temp\sql2005-logins.sql  
 

By default, Export-SqlLogin exports all logins, but you can also choose which logins you want to include or which you want to exclude by using auto-populated parameters.

export-sqllogin

Want to see sample output? Here’s sql2005-logins.sql on Gist.

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. SQL2k doesn’t exist in my prod ;).

 
    Copy-SqlLogin -Source sqlsvr2000 -Destination newsql2016  
 

Copy-SqlLogin 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, securables, permissions sets (server & db), and roles (server & db). And it does so in just a few seconds.

copy-sqllogin-ani

If you’d like a walk-through, check out the video below where I go a bit more into depth about Copy-SqlLogin, create a login, migrate the login, then log into SQL Server as the newly migrated account.

Bonus

Need to sync your login permissions for your availability groups? Sync-SqlLoginPermissions was created by request, just for that. Unlike Copy-SqlLogin, Sync-SqlLoginPermissions doesn’t add new logins. It just syncs the server & db permission sets, as well as the server & db roles and job ownership.

 
    Sync-SqlLoginPermissions -Source sql2005 -Destination sql2016  
 

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

In 2016, DBAs will begin to fully adopt PowerShell

The year 2016 is an especially exciting time 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, which left us needing to create solutions of our own. Meanwhile the Lync, Exchange and SharePoint communities were bestowed with 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. They’ve even promised new cmdlets each month! Just check out the exciting things going on with the soon-to-be-released cmdlet, Get-SqlErrorLog.

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 awesome now that PowerShell has a dedicated resource within the SQL Server team.

If you still need convincing, check out dbatools.io for some fun and powerful commands like Reset-SqlAdmin, Get-SqlServerKey, Set-SqlMaxMemory and Start-SqlMigration.

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

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server
15 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.
    Andy

    • 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.

    Navratan

  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?

    Thanks!
    Michelle

    • 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!
        Michelle

Leave a Reply

Your email address will not be published. Required fields are marked *

*