Like This Blog 0

Added by Jeff Jones November 9, 2006

Moving logins from SQL Server to SQL Server has to be done a lot.  There are a number of situations that require moving logins like: log shipping, moving a database from one server to another, or database mirroring.  In SQL Server 2000 DTS Transfer Logins didn't seem to do the job I wanted.  The SQL Server 2005 Integration Services product is closer but it doesn't move SQL login passwords.

So I wrote a set of SQL statements that creates a script of sp_addlogins to copy the passwords and SIDs across for SQL Server 2000 and another query that creates the same script for SQL Server 2005 using CREATE LOGIN.   The generated script includes Window logins, denied Windows logins and sets all server role memberships.  If you are moving a database from one instance of SQL Server to another instance this script can help.  It has been tested on SQL Server 2000 running SP3a and SQL Server 2005 running SP1.  I would run this script before detaching databases (if you are using the detach/attach approach) and then run the resulting script on the new instance after you have attached or restored the databases to the new server.

This script moves all logins (except the SA, builtin\administrators, administrative local groups (SQL Server 2005)  and nt authority\system logins) for all databases defined to an instance of SQL Server. If you are running this script with Query Analyzer or Management Studio, make sure the maximum characters per column is set to 8000.

Now for the disclaimer. I offer this script with no implied support nor has it been extensively tested.  You should thoroughly review the script generated before applying it to your system.  You can use this script for the intended purpose and also as a model for how you can use SQL to write scripts using a database table as the source.

I hope it helps.

Click HERE to get the SQL Server 2000 script file.

Click HERE to get the SQL Server 2005 script file.