Nucleus Datakonsult Sync users to logins

Home

About me and Nucleus
Partners

Articles, tips etc.
My blog
Links
Microsoft articles
Forums

Courses and training




Microsoft Most Valuable Professional


When you copy a database between two SQL Servers (using backup/restore or detach/attach) the users for SQL Server logins becomes orphaned to their respective login. This is because the connection between a user and a login is the SID value generated when you add the login to the SQL Server. This utility allow you to re-establih that connection. Just select the database and all orhpaned users are presented in a drop-down box allowing easy mapping to the corresponding login. You can also map all orpaned users in a database for which there exists a login with the name name, all in one go. Sync SQL Logins uses the 'sp_change_users_logins' system stored procedure which comes with SQL Server 7.0 and SQL Server 2000.

Versions:
This utility has been tested on SQL Server 7.0 and 2000.

Other options:
Export the logins from the sorce server and import them to the destination so they have the correct SID. Search KB for "sp_help_revlogin".

Important notice:
We do not have the resources to offer support on this utility, and it has not been tested on SQL Server 2005.
The source code is available, however.
Download Sync SQL Logins executable
Sync SQL Logins.zip

Download Sync SQL Logins source code (VS 2005, VB, FX 2.0)
SyncSQLLogins.zip
If you feel like maintaining an site with updated versions of this utility, feel free to. I suggest you drop me an email so I can link to it from this page.

Prerequisites:
  • .NET framework 2.0 or later. It is likely that this already is installed on your machine, if not you will get an error message that the application failed to initialize. You can install the framework using Windows Update, or download it from microsoft (search for "framework redistributable" at http://www.microsoft.com/downloads).
Db Maint home page:
This is a free utility from Db Maint: www.dbmaint.com.