Restoring an SQL Database on an other Server or other domain

garak

Often i hearing from customer or other programmers, they wonder why they can't connect to the restored database on new server. Only the database creater / restorer can connect.

Reason: The database creater is the dbowner. But whats with the other users.
So, the problem is, in the backup is also the user identification (SID).
But the identifer on old Server must not be the same on new server (only sa is ever the same). This Id is stored in the master database (also the case sensitivity). The same is for windows User accounts under an AD. The same account, like garak, has not the same ID / GUID on AD1 or AD2. They have different Ids. The identification, inconsequential if SQL login or windows login, is ever by id and not by name.

So only the sa, dbowner or an member of administrators can connect.

Workaround:
Before backup, make an sql script (and navision dataport with navision permission) and transfer the users and sql permissions to an, for example, file. If the users have no specific sql permission, you create an dataport to export the navision windows user (not with field SPID) and there avision permissions. Then delete the Users on database.

Restore the database on new server and import the users with an new sql script on the new server (only when specific sql permission) or with the dataport. Then the useres became autom. the right Sid.

Here is also a link to a script from MS to do this ;-)
http://support.microsoft.com/?scid=kb%3 ... &x=12&y=13

anwar0206

gr8 information helped me a lot.


Regards,
~ SA =D>

rdebath

Point of order ...

If you're restoring and using Windows logins in the same domain (but different machine is okay) you don't need this as the SIDs will be the same. However, you must successfully run a Tool->Security->Synchronize to recreate the Windows users in the master database. If it errors there will be users that cannot login.

nav_student

How you transfer menu and objects permissions?