Today, I was again requested for peer support from one of our DBA team mates who is racing against time to complete the TESTING environment refresh with LIVE data.

As part of his task remit, he is currently refreshing TEST SQL instance(s) [more that 10 SQL instances, with per instance averaging 15+ databases] with LIVE data, meaning he is restoring LIVE databases onto TEST SQL instances.

As part of this, after the restores, he wanted to map security users and logins to various roles. Meaning after the restore, ensuring the security mappings are re-set to “point-in-time before the restores” happened.

The whole exercise of carrying out a TESTING environment refresh, is to give the development team an exact representation of LIVE data for testing purposes and hence the security mappings of TESTING environment is definitely going to different from that of LIVE production.

He soon realized that there were orphaned users on majority of the databases and he could not find the script to fix the users from our normal script repository which the DBA team uses for their operational activities.

NOTE: For beginners, an orphaned user is a user in a SQL Server database that is not associated with a SQL Server login. Orphaned users are created when a database backup from one SQL instance is restored on another SQL instance.

The below code was provided to him to help fix the orphaned user, per database per instance, meaning he has to run the code on all the restored databases. The code also contains comments on how to list out orphaned users, how to fix orphaned users one by one OR how to auto fix orphaned users.



Vasudev Menon

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook