How to transfer login from one to another server

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

SQL Server 2000 Forums

SQL Server Administration (2000)

How to transfer login from one to another server

Author

Topic

mast_dba
Starting Member

15 Posts

Posted - 2006-04-18 : 13:22:28

Hi Friend,

I need to transfer databases from one server to another. Databases all ready created on new server & I just need to restore the backups from old to new. But the users (Login) r not created on new server & I need to transfer users too from old to new. Can anybody please guide me how to transfer login from one server to another. Thanks a lot for ur help.

Regards,
Mast

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 14:17:45

http://www.sqlmag.com/articles/index.cfm?articleid=16090&

Tara Kizer
aka tduggan

Krankensteins
Starting Member

24 Posts

Posted - 2006-04-18 : 14:19:03

SQL have Import&Export Wizard Thet can Help You or I think You nou more havn't ather server is thet so ?

mast_dba
Starting Member

15 Posts

Posted - 2006-04-18 : 16:10:26

hi,
Thanks a lot. I found DTS is right thing to transfer login with password.
Thanks
Mast

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 16:11:35

quote:
Originally posted by mast_dba

hi,
Thanks a lot. I found DTS is right thing to transfer login with password.
Thanks
Mast

I've only used the DTS option a couple of times in the past. It always gave me errors, which is why I now use the method in the link that I posted. It keeps the sid, so you don't even need to run sp_change_users_login after the transfer.

Tara Kizer
aka tduggan

mast_dba
Starting Member

15 Posts

Posted - 2006-04-18 : 16:45:55

Thanks Tara. I will try this too.

Mast

Krankensteins
Starting Member

24 Posts

Posted - 2006-04-19 : 03:09:09

When I restored database on other Server with out User Export then required manuali edit database sytemtables sush as "sysusers" .

mr_mist
Grunnio

1870 Posts

Posted - 2006-04-19 : 03:42:28

The link didn't work for me, it seems like the article is at

http://www.sqlmag.com/Articles/ArticleID/16090/16090.html

-------
Moo. :)

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2006-04-19 : 05:09:45

Microsoft provide 2 stored procedures to do that and in the past revisions these sp works well.
The last revision of the procedures (rev.5,April 12, 2006) that you can find here:
http://support.microsoft.com/kb/246133/
generate one error as Mr. or Miss. Sree notify here:
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=64638

The error is this:
Server: Msg 195, Level 15, State 10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.
Any idea?

Franco

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-19 : 06:43:03

What compatability level is your database setup as.
What version of SQL are you running? You may be trying to call a SQL2005 function in SQL2K.

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2006-04-19 : 08:04:54

SQL Server 2K SP4 - 2187 (8.00.2187)
In the MS KB http://support.microsoft.com/kb/246133/
there are no reference to SQL Server 2005, so I presume it has to work on SQL Server 2K.
Database compatibility level is 80.

Franco

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2006-04-20 : 09:54:59

Solution of the dilemma here:
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=64638

Franco

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources