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

Jun 14, 2016 · 4 min read  ·
Share on:

TSQL2SDAY-150x150

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

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

1Export-DbaLogin -SqlInstance 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;).

1Copy-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.

Bonus

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.

1Sync-DbaLoginPermission -Source sql2005 -Destination sql2016

Need to copy jobs for you availability groups, too? Copy-DbaJob 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 dbatools.io 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!