Why is it necessary to remove and then re-add a user to a SQL Server database after restoring it from a file?

Question

Why is it necessary to remove and then re-add a user to a SQL Server database after restoring it from a file?

If I don't do this, I get a "User login failed" when trying to access the database using this username from apps.

Answer 1

You have to do this to sync the Database user with the Server Login.

SQL first authenticates at Server Level then at a Database Level. Although, the Database login may have the same name as a Server login there is no guarantee that they are actually the same login. (After all the file backup may have come from a different server.)

By deleting and re-adding the user at Database level you are confirming that the Server login has access to the database.

Answer 2

The SUID likely changes after a restore. Read this article which has solutions explained such as how to Transfer Logins and Passwords and Resolve User Permission issues when a database is restored.
http://support.microsoft.com/kb/168001

Answer 3

I know this is very tedious to practice but we don't have any option then to remove and add the database user.

The reason behind this is - every SQL server instance has its own authentication policies that binds against the user, schemas and the roles. When you try to authenticate and access the database, the user must have proper entry in the secutiry directory of the instance, if you don't have the entry authentication fails.

So when you try to add a user and assign the database to the user, it fails because the user is already founf in the security catalogue of the database. so you first need to remove the user from the security catalogue of the database and then add it again via security directory of the SQL server instance and assign the dataabse access to the user.