One thing you will do a lot as a DBA is backup a production database and restore it to test. When your application is using a local SQL Server account, you may find that your application can no longer connect to the test environment after the restore is complete. (This is also true for user access if user accounts are local SQL Server accounts.)
This can happen if the local SQL Server account has a different SID in test than it does in prod. So when you restore the database the account at the database level has the production SID assigned to it. If the prod and test account have different SIDs it results in an orphaned user.
I have found it extremely useful to run a command to check for orphaned users as part of my normal restoration workflow. So after the database has been restored in the test environment, I run the following command against the database in test:
USE <dbname>
EXEC sp_change_users_login 'Report'
If this command returns nothing, then your accounts are not orphaned. If accounts are returned, you should run the following command for each account returned:
USE <dbname>
EXEC sp_change_users_login 'Update_One', '<account_name>, '<account_name>'
Here is a link to Microsoft's site giving more details on orphaned users:
http://msdn.microsoft.com/en-us/library/ms175475.aspx
