Orphaned SQL Server Users

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

Leave a Reply