Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
TallPaul
Starting Member
3 Posts |
Posted - 2008-02-08 : 05:13:34
|
HiI am new to SQL and still trying to find my way around.I am moving a SQL DB from one server to another. This I have done, but the users have not moved across with it.I have exported the Login list, under Security > Logins and created a .txt file.I would like to import this into the new server, but under logins there is only the option to export.I am running SQL 2000 on Intel Server 2003.Any help you can give would be appreciated.Many thanksPaul |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-08 : 05:39:41
|
then go to new server and export the logins from existing server |
|
|
TallPaul
Starting Member
3 Posts |
Posted - 2008-02-08 : 06:16:29
|
Export logins from new server?There are no logins on the new server. I want to import the logins from the old server to the new server |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-08 : 06:53:25
|
quote: Originally posted by TallPaul Export logins from new server?There are no logins on the new server. I want to import the logins from the old server to the new server
ah sorry i was having a friday afternoon moment Did you try exporting logins using SSIS Transfer logins task? |
|
|
TallPaul
Starting Member
3 Posts |
Posted - 2008-02-08 : 09:28:02
|
No - I don't know how to do that. Very new to SQL :-(I just exported the logins to a txt file and now want to import them back into the new server |
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-08 : 12:01:38
|
The following script will sync (link or create) the users of a database that has been moved. You may need to adjust passwords on the new server, but all the users will exist.Declare @Users_stmt varchar(75)Declare Users_Cursor Cursor ForSelect 'sp_change_users_login ''auto_fix'', ''' + [Name] + ''''From sysusers Where (Status = 2 Or Status = 14) And UID <> 1Open Users_CursorFetch Next From Users_Cursor Into @Users_stmtWhile @@FETCH_STATUS = 0 BeginExec (@Users_stmt)Fetch Next From Users_Cursor Into @Users_stmtEndClose Users_CursorDeallocate Users_Cursor |
|
|
X002548
Not Just a Number
15586 Posts |
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-08 : 14:09:55
|
That script works for 2k and 2k5. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-08 : 21:27:24
|
Just open a new dts package and choose copy sql logins task, very simple to follow the wizard. |
|
|
|
|
|