Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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:

.\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.

Total article views: 1538 | Views in the last 30 days: 18
 
Related Articles
FORUM

Database mirroring login failure

Database mirroring login failure

FORUM

Database Mirroring

Database Mirroring - Index on Principal

BLOG

Transferring Logins to a Database Mirror

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

FORUM

Setup a second database mirror with same principal

Can you setup two principal-mirrors for same db?

FORUM

Database Mirroring

Database Mirroring

Tags
logins    
migration    
mirroring    
powershell    
users    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones