sp_help_revlogin, as I'm sure most readers will know, is a stored procedure that scripts logins on an instance of SQL Server. It is popular because, in addition to scripting Windows logins, it can script SQL logins without changing the SID or password. This is very useful for database migrations and refreshing development database copies.
It could be easier to use though. Using the mirroring terminology of principal and mirror (one of the uses of this procedure):
- It is necessary to first install the stored procedure on the principal instance. It is not installed with SQL Server by default.
- Then open a query window on the instance, set current database to master, select Output as Text and run sp_help_revlogin
- The output scripts ALL logins on the principal. You have to edit the script to remove existing logins, or accept that you will see errors when it runs.
- It won't generate any ALTER LOGIN statements, or look at role membership.
- Only script logins that don't already exist on the mirror.
- Add the new logins to server roles if necessary
- Drop and recreate logins if the SID is different.
- Script ALTER LOGIN statements if the password or default database are different.
- Should be clever enough to detect if the default database does not exist on the mirror, or is not online.
- Script ALTER USER statements in each database if the SIDs do not match (prevents orphaned users).
- Create database users and add to database roles if necessary.
- Run from your workstation without having to explicitly connect to either instance (until you run the resulting script of course).
The logic for this is very similar to sp_help_revlogin, but it connects to both servers and does a hell of a lot more.
Most of my clients are still using PowerShell 1.0, so that's what I've used. This should work without modification in v2.0.
Copy this into a text file, and save as "ScriptLoginDiffs.ps1".
From within PowerShell, run the script as follows:
The output is displayed to the console, but can be redirected to a file as follows:
I doubt if I will need to use sp_help_revlogin again.
Read more at www.sqlcopilot.com.