tranfer a db from mssql server 2000 to msde 2000

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources