21 Aug 2009 User Resynch
Quick question for everyone this week: how do you resynch your users after a database restore?
For same server restores you do not have this issue, but when restoring from prod to test, for example, you will find that the sid’s do not match and thus you either need to resynch the users or to drop/add.
Our solution right now is to capture the syslogins every night using OpsMgr and pump the data to a warehouse. We also do a sp_helplogins on each login and pump that information to our warehouse. So, if our objects are being given grants to database roles as opposed to users (and the roles have the same names in all environments), then our resynch is as easy as pressing a button.
With SQL2008 out as well as the use of Powershell these days I thought it might be time to revisit how resynchs are being done. I am thinking ours needs some updating.