moving databases,logins

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » moving databases,logins

moving databases,logins

Rate Topic

Display Mode

Topic Options

Author

Message

srimkumarp

Posted Tuesday, November 13, 2007 6:07 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, April 23, 2008 6:03 AM
Points: 21, Visits: 108

hi, how to move databases from old server 2005 to new server 2005 and also logins.if we moved databases and logins from old server.will it contain database ,logins or not?

Post #421527

martin-210793

Posted Tuesday, November 13, 2007 7:33 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, April 04, 2013 7:05 AM
Points: 246, Visits: 260

Logins are stored in the master database,
thus you can move your database but the logins wont be moved over. the DB usernames will be though. Using these you can relink the usernames and create logins. Use inbuilt stored procedure - I can't remember the name of it. Books online should give you these.

Post #421586

Tommy Bollhofer

Posted Tuesday, November 13, 2007 7:57 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148

Refer to KB 246133 (http://support.microsoft.com/kb/246133/)

Tommy

Post #421606

SQL_ABD

Posted Tuesday, November 13, 2007 8:57 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 6:29 PM
Points: 1,326, Visits: 1,265

Hi,
Check also the following http://blogs.techrepublic.com.com/howdoi/?p=140
Regards,
Ahmed

Post #421647

Menaka-441717

Posted Tuesday, November 13, 2007 9:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 08, 2010 6:34 AM
Points: 4, Visits: 24

Hi There,

from the source server bcp out..syslogins and copy to the target server using bcp in command.Hope it will resolve your login issue.

Cheers
Menaka

Post #421661

Sugesh Kumar

Posted Tuesday, November 13, 2007 11:15 PM

Hall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:18 AM
Points: 3,461, Visits: 346

Databases can be moved using the detach and attach method. For logins there is a script in MICROSOFT site that will script your logins with password. you can use that.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Post #421914

Minaz

Posted Wednesday, November 14, 2007 2:50 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 21, 2013 10:57 PM
Points: 535, Visits: 1,423

We have done the same that sugesh suggested.

"More Green More Oxygen !! Plant a tree today"

Post #421980

Bill Prehl

Posted Friday, August 27, 2010 11:07 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 07, 2013 8:10 AM
Points: 93, Visits: 91

Since I found this thread on this topic, it should be noted that MS has two KB's regarding moving login accounts between SQL servers.

One KB is for moving logins between SQL7 to SQL2k or SQL2k to SQL2k
http://support.microsoft.com/kb/246133/

The other is for moving logins between SQL 2k5/8 to 2k5/8
http://support.microsoft.com/kb/918992/

The reason for the differences is because they changed some of there sysobject schema.

Cheers.

Bill

Post #976594

vijju.gaddam488

Posted Wednesday, August 24, 2011 6:01 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 3:17 AM
Points: 13, Visits: 141

we've big script you execute that in primary server that creates two stored procedures in master database 1.sp_hexadecimal
2.sp_revlogin

if you execute first one it give one more script for us you execute that one (result script)in secondary then you can transfer dbs.in the same way if you execute sp_revlogin you can transfer logins also.

Post #1164593

« Prev Topic | Next Topic »

Permissions