Setting Up an iMIS Testing Database: Part 2

After completing testing database update one of our developers complained that their login was not working on the testing database. I think I should describe some of our setup before getting into the solution.

We have two seperate database servers - one for production and one for testing. On each server we have a database dedicated to iMIS and another for custom web application. The custom web application database needs to retrieve information from the iMIS database; to make this possible we create a few cross-database views. These views simplify the work for our developers...

We have the same users and logins on both the testing and production databases. When I restore a backup copy of production over the testing database I end up with orphaned logins.

To fix the orphaned logins I ran the following script I found on SQLServerCentral.com

WARNING: This works for me but please read the Microsoft Books Online before proceeding. iMIS user logins are updated and corrected as part of the DB Repair Utility; the logins I am 'fixing' here are manually created for specific purposes.

-- fix Users
set nocount on
declare @v_dbuser varchar(255)
declare @sql nvarchar(255)
declare c1 cursor for
select a.name from sysusers a, master..syslogins b
where a.name = b.name
open c1
fetch c1 into @v_dbuser
while (@@FETCH_STATUS <> -1)
BEGIN
print 'Fixing User ' + @v_dbuser
set @sql = 'sp_change_users_login ''auto_fix'',' + @v_dbuser
exec sp_executesql @sql
fetch c1 into @v_dbuser END
CLOSE C1
DEALLOCATE C1