This site is currently using a generated translation

Reconnect SQL login SIDs after db restore

It's urgent, your backwards-console-based developers from the late '70s have just ordered a restore from the production database to the test environment with as many as 500 SQL logins and all with different SIDs. How is that possible you think who is always careful to script out the login so that the SID sticks with neat and tidy.

Anyway, soon release of a new version of whatever they've been tinkering with for the past few months but still, everything should be ready YESTERDAY. As a DBA, you obviously want to do as much as possible with as little effort as possible so you can get back to your important and meaningful work tasks. Then the below very simple but handy script can help, run it in the current database after restore and it will generate sp_change_users_logins for all logins that are unsynced (i.e. SID does not match between login - user). Copy the result o paste into the query window and run. If you're even lazier you can add auto execution but I'm leaning towards the more cautious side so I want to see what I'm running. Also, I don't fully trust computing machines.

DECLARE @Fixes TABLE (xcmd varchar(200),xuser varchar(100), xsid nvarchar(150));
INSERT @Fixes (xuser,xsid)
EXEC sp_change_users_login 'Report'
UPDATE @Fixes SET xcmd = 'EXEC sp_change_users_login ' + '''' + 'Update_One' + '''' + ',' + '''' + xuser + '''' + ',' + '''' + xuser + ''''
SELECT xcmd FROM @Fixes