Syncing login between database and server... an essential step to perform in database migration

After you migrate a database (or simply restore a database’s backup) to another instance, you have to make sure that the logins are working fine. However there will be cases when database user and server logins will not be in sync and hence the end user will be unavailable to access the database.

To resolve this issue, you can use sp_change_users_login to map an existing database user to a SQL Server login.

Example to automatically map database user with relevant SQL Server login:

(This is most common task required after a simple migration of a database to a new server as SQL logins of both server will have different SID.)

USE exampledatabase – Replace database name as needed

go

EXEC sp_change_users_login 'Auto_Fix', 'exampleuser' – Replace user name as needed

go

GO

Other relevant use of this procedure:

To Produces a report of the users in the current database which are not mapped with SQL Server logins:

EXEC sp_change_users_login 'Report';

Mapping a database user to a new SQL Server Login:

USE exampledatabase; – Replace database name as needed

GO

EXEC sp_change_users_login 'Update_One', '[DatabaseUser]', '[ServerUser]; -- Replace DatabaseUser with the dabase user name and ServerUser with server user name.

GO

For more details of this procedure, please refer to http://technet.microsoft.com/en-us/library/ms174378.aspx