How to: Fix Orphaned Users in SQL Server 2005/8

Frequently, we need to take backups of databases and restore them on other servers, for either disaster recovery or for development and testing.

When we restore these databases, the SQL ID's get orphaned, even if they are setup in the main database. What happens is that the main SQL ID still works and the user can be authenticated but the ID's in each database are not attached to the overall ID. In the past, I would delete each account from each database and then from the master ID, re-add each permission, which was a pain.

In SQL Server there is a stored procedure to re-attach these ID's. All of these instructions should be done as a database admin, with the restored database selected.

First, make sure that this is the problem. This will lists the orphaned users:
EXEC sp_change_users_login 'Report'
If you already have a login id and password for this sqlid, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'sqlid'
If you want to create a new login id and password for this sqlid, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'sqlid', 'login', 'password'
In general, the sqlid and the login are the same thing.

VMware & SQL

http://blogs.vmware.com/apps/2012/03/your-guide-to-virtualizing-sql-on-vsphere-part-1.html