How To Fix Orphaned Users In Sql Server

Here is an easy way of how to get a report of all orphaned users in the database and how to fix all orphan users.

--this command will give us a report of any orphaned users in the database.
sp_change_users_login 'report' 
--Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.
sp_change_users_login @Action = 'update_one',
    @UserNamePattern = 'ReportingServices',
    @LoginName = 'ReportingServices' 
--use ALTER USER instead
ALTER USER [DOMAIN\ReportingServices] WITH LOGIN = [DOMAIN\ReportingServices]
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License