topic Re: Copying *.mdf database from machine to another affects logins, in Data Management Questions

Copying *.mdf database from machine to another affects logins,

Anonymous User — Tue, 24 Sep 2013 15:35:53 GMT

Original User: Jamal432@gmail.com

Copying *.mdf database from machine to another affects logins,

I�??m wondering why coping the *.mdf to another machine makes it impossible for the users to access their databases.

Scenario:

1. A user B1 is created on the N.mdf database

[ATTACH=CONFIG]27709[/ATTACH]

2. B1 is added automatically on the users of the N.mdf and in the logins of the security of the SQL database

[ATTACH=CONFIG]27710[/ATTACH]

3. Of course, B1 is able to access the N.mdf database

[ATTACH=CONFIG]27711[/ATTACH]

4. The N.mdf is copied to another machine and attached to its databases

5. Now, B1 is not able to access the N.mdf

[ATTACH=CONFIG]27712[/ATTACH]

What might be the issue here?
Why the users are not able to access their databases as the database is copied to another machine?
How to preserve the logins as the database is copied to another machine?

Thank you

Best

Jamal

Re: Copying *.mdf database from machine to another affects logins,

VinceAngelo — Tue, 24 Sep 2013 15:55:40 GMT

Yes, adding a database with new or different user names (or even users created
in a different order) will affect login-user mapping. That's why Microsoft has a
sp_change_users_login utility.

You can't preserve userids across instances without an extraordinary amount of work
(which has to be done before they first get out of sync).

- V

Re: Copying *.mdf database from machine to another affects logins,

Anonymous User — Tue, 24 Sep 2013 17:09:48 GMT

Original User: Jamal432@gmail.com

Yes, adding a database with new or different user names (or even users created
in a different order) will affect login-user mapping. That's why Microsoft has a
sp_change_users_login utility.

You can't preserve userids across instances without an extraordinary amount of work
(which has to be done before they first get out of sync).

- V

Many thanks Vince for the help,

The other option could be to create users once again to the N.mdb database while it is on the other machine. Fortunately, the list of users is kept at user�??s folder of the security folder of the database.

In addition to that, privileges and versions are preserved as the same user name is created on the database despite the fact that the system says �??database user already exists�?�

[ATTACH=CONFIG]27716[/ATTACH], [ATTACH=CONFIG]27717[/ATTACH], [ATTACH=CONFIG]27718[/ATTACH], [ATTACH=CONFIG]27719[/ATTACH], [ATTACH=CONFIG]27720[/ATTACH]

Best

Jamal