Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Richard Fryar,
I wrote this script a couple of years ago and find it really useful, so it's about time I shared it with the rest of you!
Often you find yourself wanting to copy logins and permissions between servers. I'll use the terms principal and mirror for the rest of this description, as one of the most common uses is to keep logins in step when mirroring.
Years ago, Microsoft published a stored procedure called sp_help_revlogin to assist with this. This is useful, but it has a few drawbacks:
My script has a few advantages:
Copy the script into a file called ScriptLoginDiffs.ps1.
From Powershell (any version) navigate to the same folder and run the following:
.\ScriptLoginDiffs.ps1 PRINCIPALSERVERNAME MIRRORSERVERNAME
If you are concerned there may be SQL logins on the mirror where the password should not be changed, use this syntax:
.\ScriptLoginDiffs.ps1 PRINCIPALSERVERNAME MIRRORSERVERNAME -KeepPwd
Don't worry, it doesn't make any changes. The output is a script that can be run against the mirror. To capture the output into a .sql file to open in SSMS, use the Tee command:
.\ScriptLoginDiffs.ps1 PRINCIPALSERVERNAME MIRRORSERVERNAME | Tee OutputFileName.sql
I hope you find this useful.
It won't work if either instance is SQL Server 2000 or earlier, and I haven't tested against databases with 80 compatibility level.
Database mirroring login failure
Database Mirroring - Index on Principal
Transferring Logins to a Database Mirror
I recently discovered that my book (Pro SQL Server 2008 ...
Can you setup two principal-mirrors for same db?