Problem with user logins after db Restore

Question

I have two SQL 2005 instances that reside on different networks. I need to backup a database from instance A and restore it to a database in instance B on a weekly basis so that both databases hold the same data. After the restore, logins SIDS on database B are changed and therefore users can't log into database B and connection strings for the web application it supports are broken. Is there a work around for this? Thanks.

Answer 1

There is a stored procedure for that: sp_change_users_login (see: http://msdn.microsoft.com/en-gb/library/ms174378.aspx). In particular, take a look at the Auto_Fix action. However, be aware that this SP is going to be deprecated in the future.

Insead, we're now supposed to use ALTER USER (see: http://msdn.microsoft.com/en-gb/library/ms176060.aspx).