T-SQL Tuesday 138 – sp_help_revlogin is dead, long live sp_help_revlogin2

This month’s T-SQL Tuesday is hosted by Andy Leonard (b|t), who is asking us to write about “software changes”.

Specifically, the question is: “How Do You Respond When Technology Changes Under You?”

And my answer is:

“I try to change as well and adapt, since my work is often dependent on the changing technology”

It’s not like we have any other choice, right?

Anyways, with that obvious answer out of the way, let’s talk about something more interesting, like sp_help_revlogin.

Remember sp_help_revlogin? It’s that stored procedure that Microsoft published more than 20 years ago, that never found its way into the SQL Server built-in system procedures. Microsoft still maintains that same KB page till this day (by “maintains”, I mean copy-and-pasting it from one place to another as they change their KB platforms).

What is it anyway?

For those of you not familiar with the sp_help_revlogin stored procedure, I’ll make it brief:

• It generates CREATE LOGIN commands for one or all server logins in the instance.
• It was originally made for transferring or copying server logins from one instance to another, while retaining the same SID, password, default database, and password policy settings.
• It relies on another utility stored procedure (included in the same script) called sp_hexadecimal, for converting binary values into hexadecimal values that can be used as text.
• It does NOT include the login’s permissions and roles in its output.
• It’s long.
• It’s ugly.
• It requires you to create two unnecessary objects in your server.
• It does not work with Azure SQL Databases.

Speaking of “software changes”, a lot has changed in SQL Server since that stored procedure was written, and yet, its code has not been updated since then.

Don’t use it for migrations

If what you need is migrating server logins from one instance to another while retaining the same roles and permissions, then I urge you to forget about this procedure.

Use something like the Powershell cmdlets Copy-DbaLogin or Export-DbaLogin, part of the DbaTools Powershell module. It’s free. It’s powerful. It’s just all around awesome.

If you need to be copying server logins as part of setting up equivalent Availability Group replicas, then I recommend the even-more-awesome Sync-DbaAvailabilityGroup or Sync-DbaLoginPermission cmdlets, also part of the same DbaTools module.

Speaking of which, did you know that this was already mentioned in a past T-SQL Tuesday? What a coincidence, right?

What about Source Control?

Transferring or copying logins between instances is not necessarily the only scenario where you’d need to be generating CREATE LOGIN commands with consistent SIDs and passwords.

There are also SQL Database Projects in SSDT, where you may want to have database users as part of your source control. Those users, in turn, also require you to include a CREATE LOGIN command for their corresponding logins, otherwise the project will fail to build.

When using the built-in “Import” functionality in a SQL Database Project, you have the ability to import those users and logins as well. But unfortunately, the CREATE LOGIN commands for those logins will generate a completely random password, and not retain the original SID.

This also means that when a SQL Database project with such logins is deployed and creates these logins, they are created with the wrong password and the wrong SID. That can cause all kinds of shenanigans.

Using sp_help_revlogin could be a good solution here. Although, Export-DbaLogin could be the superior option if you also need to retain the same roles and permissions for your logins.

I give you sp_help_revlogin2

In some cases, though, you may not have the option to use external Powershell modules, and/or you don’t really need the login roles and permissions.

And yet, you also need a way around the disadvantages of sp_help_revlogin provided by Microsoft.

For example, what if you’d rather not create two unnecessary stored procedures in your master database?

Or, what if you’re using Azure SQL DB and you don’t have access to the sys.sql_logins and sys.server_principals tables?

For this, I’d like you to meet sp_help_revlogin2:

• It’s a #temporary procedure, and therefore will automatically be deleted once you disconnect from your session.
• It’s pure T-SQL, so no Powershell needed.
• Works in Azure SQL Databases (with some limitation).
• Perfect for source control and SSDT.

A few remarks:

• This procedure is relatively simple in its functionality.
• If used in Azure SQL DB, the Login Name is assumed to be identical to the database user name, which may not always be the actual case. Thus far I have found no solution for this due to the limitations in Azure SQL (but I’m open to suggestions).
• At the time of this writing, it does NOT output role membership and permissions (that may possibly change in the future, but no promises).
  • If you need to also generate database permissions, or server permissions and role memberships, you could try Kenneth Fisher’s procedures below:
    • sp_DBPermissions
    • sp_SrvPermissions
  • Both procedures above are pure T-SQL and can be created as temporary procedures. They are not likely to work with Azure SQL, though.

I hope this was helpful to you.

Good luck!

Additional Resources

• T-SQL Script to Fix Orphaned DB Users Easily
• Need help implementing SQL Server DevOps? We can help!