I'm trying to create stored procedures for performing a log shipping role change. The same logins from the primary server are already on the secondary server. Can I just use the role-change stored procedures without having to create a Data Transformation Services (DTS) package to copy the logins from the primary server to the secondary server? If I can use just the role-change stored procedures, do I need to execute the sp_resolve_logins stored procedure to resolve any mismatch between the login and the database user?
If you've already copied the accounts to the secondary server, you can just use sp_resolve_logins to hook the accounts up to the database and match the SIDs from master..sysxlogins. You have to use sp_resolve_logins when you perform the log shipping role change because SQL Server assigns a unique SID to each user account, even if the user name has been used previously on this server or others. Sp_resolve_logins resets the SIDs to reflect the new SID in each of the databases accessible to the user. When you're combining databases from multiple systems, make sure no user-account name clashes occur. You can avoid those clashes by configuring your SQL Server to use integrated security.