moving databases,logins

Question

moving databases,logins

srimkumarp

Old Hand

Points: 359
More actions
November 13, 2007 at 6:07 am

#118817

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?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

martin-210793 Ten Centuries Points: 1083 More actions · Answer 1

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.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 2

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

Tommy

Follow @sqlscribe

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 3

Hi,

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

Regards,

Ahmed

Menaka-441717 Grasshopper Points: 18 More actions · Answer 4

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

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

Minaz Amin SSChampion Points: 11052 More actions · Answer 6

We have done the same that sugesh suggested.

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

Bill Prehl SSC Enthusiast Points: 101 More actions · Answer 7

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

vijju.gaddam488 Valued Member Points: 67 More actions · Answer 8

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.