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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Import Login .TXT File

Author  Topic 

TallPaul
Starting Member

3 Posts

Posted - 2008-02-08 : 05:13:34
Hi

I 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 thanks

Paul

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
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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 For
Select 'sp_change_users_login ''auto_fix'', ''' + [Name] + ''''
From sysusers Where (Status = 2 Or Status = 14) And UID <> 1

Open Users_Cursor
Fetch Next From Users_Cursor Into @Users_stmt

While @@FETCH_STATUS = 0 Begin
Exec (@Users_stmt)
Fetch Next From Users_Cursor Into @Users_stmt
End
Close Users_Cursor
Deallocate Users_Cursor
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2008-02-08 : 12:51:38
He's using SQL 2k

Go to Enterprise manager

Right click on the database

Choose menu item

All Tasks>Generate SQL Script

Click on the last tab and look at the option to script users and logins



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 14:09:55
That script works for 2k and 2k5.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -