Site icon Eitan Blumin's blog

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?

Link back to Andy’s invitation post

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:

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:

A few remarks:

I hope this was helpful to you.

Good luck!

Additional Resources

Exit mobile version