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
 MSDE (2000)
 tranfer a db from mssql server 2000 to msde 2000

Author  Topic 

sokhay3
Starting Member

2 Posts

Posted - 2004-06-29 : 09:52:04
hello

i create a database in mssql server 2000.
some of my application need to run with msde 2000 with the same database developp in mssql server.

i find the way to transfer the data between then (copy of the files *.mdf and *.ldf) and attach the database.

the problem is that when i do this i lost my user account (even if it is existing in both database) and i cant ask the user end to go to recreate the user


is there a way to keep to user when i transfer my database from mssql server to msde (2000)??

thanks in advance for any answer or tips

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-06-29 : 10:20:52
in your case you need to run sp_change_users_login to fix the orphaned users. Look this up in BOL for the syntax. If you are running SP3 the syntax has changed a little bit and you will want to download the updated BOL from microsoft (www.microsoft.com/sql/techinfo/productdoc/2000/books.asp)

another solution is to run the following script. It will script out all the users of a database, including their GUID and password. This effectively transfers all the user information from one system to another.

Set Query analyzer to output as text and then run the script on your SQL2K system. COpy the output and run that against the MSDE copy and you should be set.


SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0




-ec
Go to Top of Page

sokhay3
Starting Member

2 Posts

Posted - 2004-06-29 : 11:18:12
thanks for your answer
i finally use the stored procedures

USE databaseName
go
EXEC sp_change_users_login 'Auto_Fix', 'userNameLost', NULL, 'pwd'
go

and i execute it from the tsql

Go to Top of Page
   

- Advertisement -