Is restore or Detach/Attach better?

Hello, everyone:

I want to move a database instance (about 50 user databases) to another server, both are running SQL Server 2000. Which method is better, retore from backup files or detach/attach? Some papers said restore amybe cause incorrect login and password transfer. Is it true?

Thanks

ZYTAll databases store the SID (Security Identifier) of the logins that have permissions on that database. When you restore or attach a database to a new instance, these users may become orphaned, if there is no login with the corresponding SID. When you create the SQL Authenticated logins on the new server, make sure you specify the SID that they should have. Windows logins have their SIDs specified by the Domain Controllers, and need not be specified in the create login statement.|||MCrowley:

Thanks for the reply. Can DTS transfer login with SID? What I am concerning is to transfer login from old server to new one.

Thanks

ZY

All databases store the SID (Security Identifier) of the logins that have permissions on that database. When you restore or attach a database to a new instance, these users may become orphaned, if there is no login with the corresponding SID. When you create the SQL Authenticated logins on the new server, make sure you specify the SID that they should have. Windows logins have their SIDs specified by the Domain Controllers, and need not be specified in the create login statement.|||Google "sp_help_revlogin" for some help to transfer sql accounts|||when the db is restored run (from the restored db)

If you do not bring logins over and create them manually

-- to view broken users
sp_change_users_login 'report' -- will give you list of "broken" users.

you then run sp_change_users_login auto_fix, 'username' for each user:

I just use this little sql script after restoring a db (make db current):

set nocount on
declare @.v_dbuser varchar(255)
declare @.sql nvarchar(255)
declare c1 cursor for
select a.name from sysusers a, master..syslogins b
where a.name = b.name
open c1
fetch c1 into @.v_dbuser
while (@.@.FETCH_STATUS <> -1)
BEGIN
print 'Fixing User ' + @.v_dbuser
set @.sql = 'sp_change_users_login ''auto_fix'',' + @.v_dbuser
exec sp_executesql @.sql
fetch c1 into @.v_dbuser
END
CLOSE C1
DEALLOCATE C1|||Nice little script! I have always done that manually...now next time we create or sync a dev database, I'll whip out your little script and be the instant envy of my peers!

Yeah, I know...I need a new set of peers.

One could probably also just add a "check every database" loop outside of this one and handle all the databases on a new server at once...though I suppose that is a pretty infrequent occurrence and probably not a justifiable expenditure of effort relative to the creation of a script to do it.