SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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)

Script Login Differences

By Richard Fryar,

Script Login Differences

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:

  • It scripts all logins on the principal, without checking if they already exist on the mirror;
  • It doesn't generate ALTER LOGIN statements;
  • It doesn't look at server role membership or server permissions;
  • It has to be installed on the principal before it can be used - some companies have rules preventing stored procedures from being added to the master database;

My script has a few advantages:

  • It compares the logins on principal and mirror and generates a script containing DROP, ALTER and CREATE statements to bring them in line;
  • It generates GRANT, REVOKE and DENY statements;
  • It keeps the passwords for SQL logins the same, unless the -KeepPwd switch is specified;
  • If the default database for a login does not exist on the mirror, or is offline, it specifies master instead;
  • It compares the SIDs in each database with the newly created logins, and corrects them if different, to prevent orphaned users;

Copy the script into a file called ScriptLoginDiffs.ps1.

From Powershell (any version) navigate to the same folder and run the following:


If you are concerned there may be SQL logins on the mirror where the password should not be changed, use this syntax:


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.

Total article views: 2607 | Views in the last 30 days: 0
Related Articles

Database mirroring login failure

Database mirroring login failure


Database Mirroring

Database Mirroring - Index on Principal


Transferring Logins to a Database Mirror

Transferring Logins to a Database Mirror I recently discovered that my book (Pro SQL Server 2008 ...


Setup a second database mirror with same principal

Can you setup two principal-mirrors for same db?


Database Mirroring

Database Mirroring