I set up a log-shipping stand-by server very carefully (I thought) but at least some of the SQL logins don't work. As I understand it, a database user has an ID that points to the server login. An un-enforced foreign key. So I found out the IDs of the logins on the live server and scripted them as
I was feeling pretty smug but today we had to fail over one of the databases and two of the logins got a 'login failed' error. After I ran sp_change_users_login 'AUTO_FIX' with the username and password it was all sorted. But I thought I'd got round this in the way I set the server up.
Where did I go wrong, please?
asked Dec 04, 2009 at 11:56 AM in Default
I suspect that even though you created the SQL Server Logins on the standby server, SQL Server would not have been able to map the Logins to Database Users because log shipping would have not permitted this i.e. the log shipped database would have been either set to read only or placed in a restoring state.
This is why when you bring the log shipped database online, the stored procedure sp_change_users_login 'AUTO_FIX' is able to map the logins to database users successfully.
answered Dec 04, 2009 at 02:01 PM
I am not sure I understand here, you mention sp_change_users_login wich should not be needed if you have created the logins with the correct SID's, and that sp does not take pwd as a parameter. But since you also mention password, I wonder if the error simply was that the passwords for your correctly SID-matched logins didnt match in your 2 systems?
answered Jan 11, 2010 at 12:11 PM