Moving Logins Across Multiple SQL Servers

Home > Blogs > SQL Server > Moving Logins Across Multiple SQL Servers

Moving Logins Across Multiple SQL Servers

Like This Blog 0 Jeff Jones
Added by 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.

Videos You May Like

A Simple Introduction to Cisco CML2

0 3698 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

How to create a Cisco IOS Banner – Login and MOTD Message of the Day

0 4239 4

In this video, Cisco CCNA instructor Mark Jacob shows how to create a Login and Message of the Day (MOTD) banners in Cisco IOS. The Banner is an interesting feature of the Cisco IOS. You could probably get by without it, but in a commercial environment you want to have it.

OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj

0 247 1

In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.