Situation:
Development uses MSSQL for the application. The demo application CD uses
MSDE and needs to ship with data that came from the developments MSSQL
server.

What I've tried:
I can just copy and mount the MDF and LDF files, and mount them on the MSDE
system. However the only one that has access to them is sa. When I look at
the database, I see the other users, and the tables that they own. However,
they do not have login rights to the SQL server. If I set them up with
login rights on the SQL server, I cannot allow them access to the database
because I receive an error that there is already a user with the same name.
Thus they are two different users with the same name.

Any ideas?

Thanks,
Jack T.

You can use BACKUP and RESTORE.

The reason you cannot add back your users as you have discovered is that
although the destination SQL Server has a login of XX and your DB has a
login of XX, to SQL Server these are not the same thing.

You either need to use

Exec sp_revokedbaccess 'User Name'
Exec sp_grantdbaccess 'XX','User Name'


Or you can use

sp_change_users_login



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Jack T." <71045.3122_No_Spam_Here (AT) compuserve (DOT) com> wrote

I tried that. I wasn't able to make it work. The BACKUP seems to have the
path embedded because on restore it wants to overwrite or append the files
in the same location that the backup came from. If there is a way to do
this, that would be great.

I will look at revoke and grant.

Thanks tons,
Jack T.

Have a look at the WITH MOVE option to the RESTORE cmd

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Jack T." <71045.3122_No_Spam_Here (AT) compuserve (DOT) com> wrote

Fabulous! I'll take a look.

Thanks,
Jack T.