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.)
(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
Comments