When migrating to new server, a common challenge to administrators is importing existing login/users to the new server. This maybe tricky since there is no one click option in Enterprise Manager to this task. If migrating to the same version of SQL, the best way is by using Data Transformation Services (DTS). But this morning our migration team was facing a little bit challenge. The scenario was:

- Our server data disk is failing
- Status of the database is "suspect". We manage to make it online by using this method but not all data was readable.
- We have an updated backup and the team able to restore it on the new server.

After the restoration, everything was fine except the login/users were not successfully restored. All users on the database had no login name. This is major problem since our application's security is fully dependent on these SQL users and we have around 100+ users so encoding each one of it is very tedious.



After several testing we were able to come up with a better way to restore the users by using Data Transformation Services (DTS) again, but this time with a different settings.

Here are the step by step process we did:

1. Open the Data Transformation Services From the SQL Program Menu



2. Choose the data source



3. Specify the destination server



4. Select "Copy objects and data between SQL server databases"



5. On the select objects to copy page check only the one shown below:



6. On the copy advanced options, check all options



7. Proceed by clicking next or finish button

Using these steps, user roles and properties were successfully imported to the new server. Thats It! another problem solved.

Continue Reading...

Labels: Common Problem Fixed, Microsoft SQL Server