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 3902 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

Creating Dynamic DNS in Network Environments

0 646 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader teaches how to create Dynamic DNS zones in Network Environments. Video Transcription: Now that we’ve installed DNS, we’ve created our DNS zones, the next step is now, how do we produce those … Continue reading Creating Dynamic DNS in Network Environments

Cable Testers and How to Use them in Network Environments

0 735 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

Write a Comment

Share your thoughts...

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