Understanding and processing orphaned users in a SQL Server database

Problem

Attaching and restoring a database from one server instance to another is a common task that database administrators perform. After attaching or recovering a database, the logins previously created and configured in the database are no longer accessible. The most common symptom of this problem is that the application encounters a logon failure error, or that when you try to add a login to the database, you may get a message that the user already exists in the database. When you perform an additional or a recovery, this is a very common situation, so how do you solve the problem?

Expert answers

When a database migrates from one server to another, the login IDs stored in the master-slave database do not match the login IDs stored in each user database. As mentioned above, some of the errors you will see after attaching or recovering a database include:

%s Permission denied on object%.*s, database%.*s, owner%.*s

Or

Microsoft SQL-DMO (ODBC sqlstate:42000) Error 15023:user or role '%s ' already exists to the current database.

Without proper understanding and proper planning, you may encounter this problem. You may delete and recreate this user, but you will release all configured permissions. So a proper link mechanism is needed, so you want to retain permissions.

Some of the possible error messages you may see include