From time to time I have run into the annoying issue of orphaned SQL Server database users and decided to pass on some useful information on how to resolve this situation.

The most common operation that will result in one or more orphaned database users is performing a database restore from a backup file to a SQL Server installation different than the one from which the original backup was created; maybe while attempting to migrate an entire database from DEV to PROD. This is the result of the fact that the SID of the login on the new system does not match the SID of the corresponding login on the original system. This breaks the mapping between the login and the database user.

There are some system stored procedures that can be used to detect and correct any orphaned user situations.

For SQL Server 2000: You can use sp_change_users_login to detect and generate a list of orphaned database users.

USE <database_name>
GO
sp_change_users_login @Action=’Report’
GO

If any orphaned users are reported, you can use sp_change_users_login again to fix them on a case by case basis. Be aware that sp_change_users_login only allows you to update regualar SQL database logins, not Windows account logins.

USE <database_name>
GO
sp_change_users_login @Action=’update_one’, @UserNamePattern=’<database_user>’,
@LoginName=’<login_name>’
GO

For SQL Server 2005 & 2008: The ALTER USER procedure has been provided and is intended to take the place of sp_change_users_login (as of SQL 2005 SP2).

ALTER USER provides the same basic user management functionality as sp_change_users_login with the addition of being able to also affect Windows logins.

USE <database_name>
GO
ALTER USER <database_user> WITH LOGIN=<login_name>
GO