It’s usually a good idea to schedule regular refreshes of a development (dev) sql server from the production(prod) version. Ideally, the prod environment is secured from everyone except an isolated number of systems administrators requiring dev to be kept as close to the live version as possible. While performing regular refreshes of our dev sql servers I have come across several useful resources.

The main problem that many administrators encounter during the process is orphaned users. This occurs because the instance's user accounts end up with different SID then the ones in the database.

Microsoft provides a stored procedure in 2005 to correct the problem.
1sp_change_users_login (Transact-SQL)

The report argument will return any orphaned users within the current database.

The auto_fix argument will correct the user specified so that it’s sid matches that of the instances if one exists. If one does not exist you can use 2sp_addlogin to generate the user quickly.

A great script is available via a 3blogger site. The small script corrects any orphaned accounts in a specified database.

Lastly, 4
Microsoft provides a few nice stored procedures available for download that will generate create scripts for all the users in your environment. It can elevate the need for relinking orphaned accounts when performing refreshes.
Install the stored procedure and execute sp_help_revlogin.

1. 1. http://technet.microsoft.com/en-us/library/ms174378.aspx
2. 2. http://technet.microsoft.com/en-us/library/ms173768.aspx
3. 3. http://blogs.msdn.com/blakhani/archive/2008/02/25/script-map-all-orphan-users-to-logins-else-create-login-and-map.aspx
4. 4. http://support.microsoft.com/kb/918992/