SQL Server – Fix orphaned SQL users

Once you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the users.

The problem is that the user in the database is an “orphan”. This means that there is no login id or password associated with the database user

So before accessing database we need to maps an existing database user to a SQL Server login. For that use sp_change_users_login to link a database user in the current database with a SQL Server login. Using following steps we can fix orphaned SQL users

First we have to identify the “orphan” users. Below will lists the orphaned users:

EXEC  sp_change_users_login  'Report'
Go

If you have login ID myUser in security of Server and you db have user mydbUser then

EXEC  sp_change_users_login  'Update_One',  ‘mydbUser’,  ‘myUser’;
GO

If you want to create a new login id and password for this user, fix it by doing:

Exec  sp_change_users_login  ‘Auto_fix’, ’mydbUser’, ’myUser’, ’mypassword’;
Go

For detail see the
http://msdn.microsoft.com/en-us/library/ms174378.aspx