The Scenario:
You’re working on a SQL database locally and when you restore the database on the production server the login no longer works. The user has been orphaned.
Solution #1 – Delete and recreate the user from scratch
Solution #2 – Restore the orphaned user
Check to see if the server registers any orphaned users. Open SQL Server Management Studio and select the database you restored. Run this stored procedure:
EXEC sp_change_users_login 'Report'
The resulting list will confirm what users are orphaned, if any. As long as the Login exists under the SQL Server Security -> logins you can reattach the user to the login by running this stored procedure:
EXEC sp_change_users_login 'Auto_Fix', '{user name}'
If the login doesn’t exist under the SQL Server Security -> logins you can create a new login for the user using the following stored procedure:
EXEC sp_change_users_login 'Auto_Fix', '{user}', '{login}', '{password}'
2 comments for “Orphaned SQL Server Users”